VBA Conditional Formatting

[Lord_B]

Hi,

Thanks again for taking time to look at this.

What I am trying to do is change the colour of the above cell depending if an S is in the below cell.

I already have a vlookup formula in the above cell which depending on the value will change colour.

This is easy to do with conditional formatting but the issue with that is I have a range from D1 to UD207 which means there will be 38,500 entries.

So the question is, is there a way to do this with VBA?

Thanks for all your help.

Ben

 

[Joe4]

This is easy to do with conditional formatting but the issue with that is I have a range from D1 to UD207 which means there will be 38,500 entries.

It is just as easy to apply Conditional Formatting to a whole range of cells as it is to apply it to one cell.
Just select the whole range you want to apply it to, and write the Conditional Formatting formula as it applies to the very first cell in your selection.
As long as you code your absolute/relative cell references correctly, the Conditional Formatting formulas will automatically adjust for all the others.

 

[Joe4]

So, if you wanted to highlight a cell if the cell below it is an "S", just select your whole range (D1:UD207) and enter this CF formula:

=D2="S"

and choose your formatting color.

The CF rule with then be applied to all the cells in your selection, and every cell will look at the cell just below it.

 

[Lord_B]

Hi Joe4,

I just face palmed myself for trying to over complicate it!

Thanks for the above . great example.

Must remember KISS, Keep It Simple Stupid.

Thanks again.

Ben

 

[Joe4]

You are welcome.
Glad I was able to help!