cell color based on value

Shweta

Active Member
hi,
how to highlight the excel cells based on value present in another column or same column. Example-

cola colb
25-Aug b
26-Aug c
27-Aug d
28-Aug e
29-Aug y

1. To highlight col b cells where col b does not contains text "d". So, rest all in col b to be highlighted.How to do this?
2. To highlight the col a cells where value in col a > current date. How to do this?
3. Where ever there is "e" written in col b, highlight the respective col A cell in yellow (28-Aug). How to do this?
 
Last edited:

sahil_raina_91

Active Member
Insert a new column Highlight in the collection, so it appears :

cola colb Highlight
25-Aug b
26-Aug c
27-Aug d
28-Aug e
29-Aug y

Loop the collection
Create 3 decisions which connect one after the other
1. Insert Highlight=b if colb =/= d
2. Append Highlight=a if cola>current date
3. Append Highlight=a if colb=e
Loop End

After Loop End, your collection should looks like this:

cola colb Highlight
25-Aug b b
26-Aug c b
27-Aug d
28-Aug e baa
29-Aug y ba

Finally Loop this collection again,
Look at the Highlight column values, and
if it contains a = Highlight cell A
if it contains b = Highlight cell B
______________________________________

All the above can be simply done via Conditional Formatting/Macro too but that will require you to build a code.
 

Shweta

Active Member
I have more than 6K records. And I am not using collection. I need to directly do that in excel.
 
Top