Excel 2007 : Format previous cell based on current cell

**IamHarish** · 12-03-2011, 11:29 AM

I need it done through VBA!

In column A the names are followed by their presence (Absent/Present),
I just want to color the names based on present or absent.

So Sunny was present so color that cell in Green
& Rocky was absent so mark him in Red

A1 Names (Header)
A2 Sunny
A3 Present
A4 Sam
A5 Present
A6 Tom
A7 Present
A8 Rocky
A9 Absent
A10 Raw
A11 Absent
A12 Dave
A13 Absent

**Richard Buttrey** · 12-03-2011, 11:37 AM

Hi,

Use conditional formatting. Create two Cond. formats

Please Login or Register  to view this content.

set the colour to red and apply it to A$3:A$13
Do the same with the second Cond. format

Please Login or Register  to view this content.

and colour it green.

Regards

**Richard Buttrey** · 12-03-2011, 11:39 AM

I see there was a late edit. Why use the slower VBA route when there's a perfectly efficient conditional format option?

**IamHarish** · 12-03-2011, 11:49 AM

Dear! I want previous cell to be formatted not the current cell.
If A3 contains "Present" only then format A2.
and Why VBA - Coz this is small part of my project, where as I have a VBA to do many several things & along with that code I need this to be added, VBA code is much appreciated!, thanks!

**Richard Buttrey** · 12-03-2011, 12:45 PM

Sorry I should have clarified. That CF should go in A2 and refer to A$3:A$13. I know you want it applied to the name cell not the Presence or Absence cell.