How do I make excel change the colour of a cell depending on a different cells date?

wjwhittle , you are welcome

SergeiBaklan Thank you! works perfectly

wjwhittle 

For the blue you may use conditional formatting rule with formula

=COUNTIF($H:$H,B1)

applied to $B$1:$F$50.

Another similar rule is for red.

When I insert a column - it seems to inherit the formatting - so its AutoMagic !
I keep the most current stats on the left of the sheet next to the legend - the Old data moves out to the right ... 

MrKasifs 

That means you may apply the rule to the range $B$2:$XFD$1000000 if you don't care about performance, but it's better to take more realistic gap.

MrKasifs 

In previous post

"Tomorrow I will insert the column for the October 9th"

That means you are expanding the range, isn't it? Or you always have exactly the same number of columns?

MrKasifs 

You may expand you range to the right as necessary (here is to AZ column) and additionally check if next cell is not empty

Rule iterates all cells within the range. Since we use relative references, B2 is compared with C2, C2 with D2, D2 with E2, etc. to the right and down.

You may check in attached file.

MrKasifs 

In first case since the range you applied the rule starts from C2, condition also shall start from it, i.e. 

Cell value > C2

Cell value < C2

MrKasifs 

You set up the rule incorrectly - as you can see in your first screenshot, the rules apply to C2:K19.

Since they refer to A2, the rules should have applied to B2:K19 (or even further to the right)...

HansVogelaar

No, the requirements have not changed - you will observe that the values in the yellow boxed sections are incorrectly highlighted - because 
D2 is < E2 = Green not Red
J2 < K2 
G6 < H6 

So I need to set up the conditional formatting for Every Column relative to the previous column. 
BUT At least I can use the Format painter to apply it. 

 Thank you, 

HansVogelaar 

I had a feeling it was not a simple answer.  I greatly appreciate the help.  I will try out the macro in the Visual Basic Editor.

Scottsgt739 

Excel does not have a built-in way to do this. This will require VBA code.

Copy the following macro into a module in the Visual Basic Editor:

Sub ColorCells()
    Const shn1 = "Sheet1" ' Name of first sheet
    Const shn2 = "Sheet2" ' Name of second sheet
    Const col1 = "A"      ' Column on first sheet
    Const col2 = "A"      ' Column on second sheet
    Dim wsh1 As Worksheet ' First sheet
    Dim wsh2 As Worksheet ' Second sheet
    Dim rng1 As Range     ' Cell on first sheet
    Dim rng2 As Range     ' Cell on second sheet
    Application.ScreenUpdating = False
    Set wsh1 = Worksheets(shn1)
    Set wsh2 = Worksheets(shn2)
    wsh2.Columns(col2).Interior.ColorIndex = xlColorIndexNone
    For Each rng2 In Intersect(wsh2.Columns(col2), wsh2.UsedRange)
        Set rng1 = wsh1.Columns(col1).Find(What:=rng2.Value, LookAt:=xlWhole)
        If Not rng1 Is Nothing Then
            If rng1.Interior.ColorIndex <> xlColorIndexNone Then
                rng2.Interior.Color = rng1.Interior.Color
            End If
        End If
    Next rng2
    Application.ScreenUpdating = True
End Sub

Change the constants at the beginning of the macro as needed.

Run the macro whenever you want to update the colors on the second sheet.

Scottsgt739 

Have the names on Sheet1 been colored manually or by a conditional formatting rule?

MrKasifs 

If your requirements have changed, please explain them again in detail, preferably with an example.

MrKasifs 

Yes - once again, that is exactly what the rule that I proposed does.

The formula =A2 applies to cell B2. In this formula, both the column reference A and the row reference 2 are relative. This means that Excel will automatically adjust them for other rows and columns.

So in B3, Excel will use =A3, in B4, it will use =A4 etc.

And  in C3, Excel will use =B3, in D3, it will use =C3 etc.

If I had used =$A$2, the column and row references would have been absolute, i.e. fixed.

MrKasifs 

Yes, that's how I interpreted your question. The conditional formatting rule that I proposed should do that.