VBA to highlight in colour if criteria is met

[leemcder]

Hi, I'm hoping someone can help me with a macro which will highlight a value in column C based on certain criteria

So all the criteria must be met on each row, so if L2=True and M2=False and C2>=1 and if D2>=1 then highlight cell C2 yellow but if D2=0 then highlight C2 Green. I want it to do this on every row there is data

I'm not experienced at all in VBA and I've spent hours trying to work out the best way of doing this.

Many Thanks

 

[CROY1985]

I think you can do this with conditional formatting:

If D2 = 0 should C2 be green no matter what?

 

[CROY1985]

Select cell c1:

1st rule:
Conditional Formatting > highlight cells rules > more rules > use a formula:

=C1=0

^Set the format for this one to green

=AND(L1=TRUE(),M1=FALSE(),C1>=1,D1>=1)

^set the format for this to yellow

Last step is to apply it to rest of your data, so go into:

Conditional formatting > Manage Rules > (Select "this Worksheet" from the dropdown)

under applies to ensure this covers all of your data (column C only) i.e if you have 100 rows of data this would be C1:c100.

Hope this helps

 

[leemcder]

I think you can do this with conditional formatting:

If D2 = 0 should C2 be green no matter what?

Thanks for the reply, I have looked at conditional formatting and I am struggling with the formula. C2 should only be green if L2="True" and M2="False" and D2 is 0 but I want C2 to turn Yellow if

L2="True" and M2="False" and D2 is =>1

Hope that makes sense?
​

 

[CROY1985]

what about the value in c2? does that need to be >1 for both conditions or just the yellow condition?

 

[CROY1985]

if value of c2 is irrelevant then the 2 formulas you need are:

Green
=AND(L2=TRUE(),M2=FALSE(),D2=0)

Yellow
=AND(L2=TRUE(),M2=FALSE(),D2>=1)

 

[leemcder]

what about the value in c2? does that need to be >1 for both conditions or just the yellow condition?

Cell C2 should always be equal to or more than 1 for both conditions. , if it is zero then it should not be coloured. If the conditions are met in L2 "True" and M2 "False" and C2 is >=1 and D2>=1 then it should highlight yellow but if D2 is 0 with the same criteria's met then it should go green.

 

[CROY1985]

Ok, you just need to add "C2>=1" into the two formulas above and you should be good.

 

[leemcder]

Ok, you just need to add "C2>=1" into the two formulas above and you should be good.

That worked perfectly. Thank you very much for you help with this. Much appreciated