VBA or Formula to set cell colour based on an IF condition

[lee2121]

Hi I'm looking for a way to dot he following without using conditional formatting.


if cell K3 to W3 value is equal to or less than cell J3+X3 then set the background colour of cell K3 to W3 to Red

Cell K3 to W3 is just a range and the formula would need to work from the individual cells within that range and not sum the cells.
I have done this using conditional formatting but it's very hard to create a different new formula for each cell i need checking so i was hoping i could do this either directly ito the sheet using a fomrula or using VBA.

Regards

Lee

[Akuini]

I need more info.

if cell K3 to W3 value is equal to or less

do you mean each cell value or total value (sum) of cell K3 to W3

What about blank cell in K3 to W3, should it be red too?

[Tinbendr]

I still think conditional formatting is the better of the two ways, so I've included both in your example.

Conditional formatting. Select the whole range that you want red then enter this formula.

Please Login or Register  to view this content.

VBA: In the sheet code page.

Please Login or Register  to view this content.

[lee2121]

Sorry it's the individual cell value so,

if K3 is equal to or less than J3+X3 then cell K3 turns RED.

on the next row it would change to

if K4 is equal to or less than J4+X3 then cell K4 turns RED.

Also it would need to work accross columns such as below

if L3 is equal to or less than J3+X3 then cell L3 turns RED.

[Akuini]

Try this:

Please Login or Register  to view this content.

[lee2121]

Sorry just noticed i have the formula wrong, it should be

if K4 is equal to or less than J1+X3 then cell K4 turns RED and so on...

J1 will only change accross when moving cells so K6 it would be M1

[Akuini]

if K4 is equal to or less than J1+X3 then cell K4 turns RED and so on...

more example, please.

J1 will only change accross when moving cells so K6 it would be M1

why K6 would be M1?
and J1 is not numeric.

Could you attach more sample? showing the result you expect

[lee2121]

Please see attached.

[Akuini]

Try this:
In cell Q4, why is it red?

Please Login or Register  to view this content.

[lee2121]

Thank you the code works really well!

I have tried to adapt it for the master file i have but im pretty new at this and can't seem to get it to work. Could you please adapt your code for me to work on the new file?

Please note on the new file K3 now starts at K4 and X3 is now AQ4

Regards

Lee

[Akuini]

Ok, try this

Please Login or Register  to view this content.