PDA

View Full Version : VBA - Set background color (and text) according to column in another dataset



UronDidas
09-12-2019, 01:03 PM
Hi,

I have to admit that I am a beginner in VBA. I need a VBA tool to set the background color and text in a column according to a specific comparison to a number column in another dataset.

I have two excel files. One is the source, the other the result. They both have more than one sheet, however, only the one which opens when the file is double-clicked is important (but would be great to somehow tell the vba how the sheet to be used is named, so that I do not have to delete the other sheets in the worksheet in order to get the vba running).

Source file: (C:\Source.xlsx)
25031

Result file: (C:\Result.xlsx)
25032

The "Result.xlsx" file already exists, however here column action in the result folder is filled to help explain how the calculated result should look like. This would be empty. But the other columns are existing already.

I want to create a VBA excel file, which I put into the same folder as the source and the result file. The VBA file should run when I click on generate and fill the action column according to specific rules (see picture below how I imagine it could look like, or also attached):

1.) The main point is to fill in the action column in the result file, depending on the number column in the source file for each specific ID. So the calculation needs to be done for the matching ID.



if the number is empty then fill in the number column in the source file with "Text B" and yellow background color.
if number is smaller than threshold 1 (but not empty, 0 is also accepted as a valid number) leave the field in the action column empty
if number is larger (or equal) to the threshold 1, put into "Text A" with orange background-color
if the column variable b in the result file has red background color with text "ACTION NOT NEEDED", then the action column should be filled with the text "action needed?" with red background color, regardless of the number in the source file
if the column variable b in the result file has red background color without text "ACTION NOT NEEDED", then the action column should be filled just with red background color (also regardless of the number in the source file)
if the column variable b in the result file has a blue background color (regardless what text is in), then the action column should be filled just with blue background color (also regardless of the number in the source file)
if the column variable a in the result file has a light green background color (regardless what text is in), then the action column should be filled with text "more info needed" (also regardless of the number in the source file)
special case is if the category variable in the source file shows "F":

if the number is smaller than the threshold 1 (but not empty, 0 is also accepted as a valid number) then the action field should be empty (this case is not contained in the above screenshot)
if the number is larger than the threshold 1 the action column in the result file should be filled with text "decision pending"
if no number is given (so the number field is empty), then it needs to be filled with "Text B" and yellow background-color




I do not want to specify a certain RGB color in the code for the background colors. I want to "read in" the background colors. So my VBA file should look like this, also the threshold value should be dynamic and not static in the code. Also, the specification where the number column is and where the action column should be filled has to be given (same for variable a and b column). (As the source and result files are quite large, this can be also something like "BX" or so, here for simplicity its F, E, C, and D):


25037

When generate is clicked, I want the code to create a file Result_filled which is the result file with the filled action column.

Any help is much appreciated!