Conditional Formatting using another cell value

[KathrynJ]

I have 2 columns that are date fields. I want to shade the cells in column B in different colors based on the number of days between the values in each column. Attached is sample file and list of rules. I can't seem to get the conditional formatting to work. Once that's working, I then want to write a macro to do this for me each time I load new data into the worksheet. Any ideas?

 

[p45cal]

instead of:
.Color = 3367985
use:
.Color = RGB(49, 100, 51)

 

[KathrynJ]

Than you, but what I really need is the formula to use within the conditional formatting function in order to make this happen based on the rules listed on my sample file.

 

[p45cal]

Sorry! That was an answer to another thread!

 

[p45cal]

run this macro while the sheet in question is the active sheet, then examone the conditional formatting formulae and look at the defined Names in the workbook.

Sub blah()
myColours = Array(65535, 5296274, 15773696, 49407, 255)  'yellow,green,blue,orange,red.
With ActiveSheet
  lr = .Cells(.Rows.Count, "B").End(xlUp).Row
  .Parent.Names.Add Name:="Thresholds", RefersToR1C1:="={0.5;184;366;1461;3561}" 'your 5 thresholds in ascending order.
  .Columns("B").FormatConditions.Delete
  .Range("B7").Activate
  With .Range("B7:B" & lr)
    j = 1
    For i = LBound(myColours) To UBound(myColours)
      With .FormatConditions.Add(Type:=xlExpression, Formula1:="=MATCH($A7-$B7,Thresholds)=" & j)
        .Interior.Color = myColours(i)
      End With
      j = j + 1
    Next i
  End With
End With
End Sub

Come back if you need more explanation.

In the attached, I've only added the macro to your file and haven't run it. Press Alt+F8 on the keyboard, choose blah, and Run.