VBA If Statement to apply Background Colour

[SimonGeoghegan]

Hi All,

I am trying to figure out a way that I can apply a background colour to a range of cells (which will change on a monthly basis) based on the colour of the previous range of cells.

So, if the previous range of cells is coloured green, I want the new range of cells to be coloured red (and vice versa)

I can select the range of cells I need, and I know how to apply a background colour however I am struggling with understanding how I can get VBA to read the background colour of another cell and then use that to determine what colour my new range should be.

Can anybody advise of how an if statement would look for this?

The code I have got so far is:

    'Open Tabs and Copy Hospitals
'
    Sheets("Data").Visible = True
    Sheets("Data").Select
    Sheets("Brain").Visible = True
    Sheets("Brain").Select
    Range("B8:B64").Copy
    Sheets("Data").Select
    Range("A3").Select
    ActiveCell.End(xlDown).Offset(1, 0).Select
    
    'Paste Hospitals
    
    Selection.PasteSpecial Paste:=xlPasteValues, _
    Operation:=xlNone, SkipBlanks:=False, Transpose:=False

At this point, the range of cells I want to apply a background colour to is selected, however from I'm struggling.

Any help would be greatly appreciated

Regards,
Simon

 

[SimonGeoghegan]

I have managed to figure this out myself now so no help required