VBA code to colour cell based on multiple criteria

[mergim]

Hello all,

I try to colour cells "red" based on two criteria (see formula below) - the first is the lookup value (E2) should be equal to "TENSILE", the second is that the number in I2 is below 25. If these criteria are met/TRUE, then the rows in column i/I should be given the colour red.

=AND(XLOOKUP(E2;Database!$A:$A;Database!$B:$B)="TENSILE";I2<25)

I have tried conditional formatting, but this does not work as the data I work with is a table. Secondly, I use a button to load data from another document to my active document. This screw up when I try to implement it.

I hope you can help, thanks!

 

[jdellasala]

Conditional Formatting should be able to handle that fine (I assume your computer's local settings uses a semicolon ( ; ) instead of a comma (,) to separate parameters.
By coincidence this video was just 5 hours ago, has an excellent example of how to deal with conditional formatting, and includes a sample file with before and after worksheets.

The board has great formatting tools to make it easier for others to help so that formulas, VBA, M Code etc. are easier to read and copy, and a great Excel Add-In called XL2BB to allow copying of data from a worksheet and pasting it to the board making it that much easier for others to help. Give them a try!

 

[mergim]

Conditional Formatting should be able to handle that fine (I assume your computer's local settings uses a semicolon ( ; ) instead of a comma (,) to separate parameters.
By coincidence this video was just 5 hours ago, has an excellent example of how to deal with conditional formatting, and includes a sample file with before and after worksheets.

The board has great formatting tools to make it easier for others to help so that formulas, VBA, M Code etc. are easier to read and copy, and a great Excel Add-In called XL2BB to allow copying of data from a worksheet and pasting it to the board making it that much easier for others to help. Give them a try!

Hello,

Unfortunately it does not help. To elaborate more on this issue. I have this excel button where by pressing it you can extract data from another document and then load it into your main file. If I first extract and load the data into my main file, and then make the conditional formatting, then it works. However, if I do the conditional formatting before I extract and load the data, then the formatting will not apply. See picture below.

From row 2-15, the conditional formatting applies, because I did the formula after I loaded the data. It is important to mention that the range to format is set to be I:I, so the whole column i.
But when I load the second time, the conditional formatting won't apply, which you can see on row 16-29. Row 24 and 26 should also have the color orange, but for some reason they don't. The range does not act dynamically for some reason

 

[jdellasala]

Hello,

Unfortunately it does not help. To elaborate more on this issue. I have this excel button where by pressing it you can extract data from another document and then load it into your main file. If I first extract and load the data into my main file, and then make the conditional formatting, then it works. However, if I do the conditional formatting before I extract and load the data, then the formatting will not apply. See picture below.

From row 2-15, the conditional formatting applies, because I did the formula after I loaded the data. It is important to mention that the range to format is set to be I:I, so the whole column i.
But when I load the second time, the conditional formatting won't apply, which you can see on row 16-29. Row 24 and 26 should also have the color orange, but for some reason they don't. The range does not act dynamically for some reason

View attachment 80779

Assuming by table you mean an Excel table, have you tried using Table references? Also, please use XL2BB when posting data.

 

[mergim]

Assuming by table you mean an Excel table, have you tried using Table references? Also, please use XL2BB when posting data.

Hello,

I am not sure how to use table references here? For me it seems weird that the conditional formatting is not acting dynamically.

 

[jdellasala]

Hello,

I am not sure how to use table references here? For me it seems weird that the conditional formatting is not acting dynamically.

Post your data.