Comditional Formatting

[Tom90]

Hi All I am looking for some help with Conditional Formatting, I have cell “A1” and “B1” and want cell “A1” to go Green if lower than the value in “B1” or Red if higher than the value “B1” that part I can do ok but when I have a value in cell “A1” and cell “B1” is blank the formatting still works in cell “A1” how can I make cell "A1" stay the same with no colour in it until I put a value in cell ”B1” to change it to Green or Red, the values I am working with are dates
Thanks Tom90

 

[Somendra Misra]

Hi Tom90,

Try this formula:

=AND($A1<$B1,$B1<>"")---Format Green
=AND($A1>$B1,$B1<>"") ---- Format Red.

Regards,

 

[Tom90]

Hi SM,
That works a treat

Ta, Tom90

 

[Somendra Misra]

@Tom90

Glad you solved it. Welcome Back anytime.

Regards,

 

[Tom90]

Hi SM,
I am trying to take it to the next stage as I have 500 cell to put the Conditional Formatting into I have got a macro loop but not too sure how to put your formula into it, if I put it in pointing to cell 1 then all the other 500 cells point to cell one can you help and let me know how I can loop it the column, I have put me code below

Dim RBD As String, Red As Integer, Green As Integer
For i = 3 To 500
Sheets("Jan_14").Select
Range("H" & i).Activate
Range("K" & i).Activate
  Range("H" & i).Select
  Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
  "=AND(H<K,K<>"" "")"
  Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
  With Selection.FormatConditions(1).Font
  .Bold = True
  .Italic = False
  .ThemeColor = xlThemeColorDark1
  .TintAndShade = 0
  End With
  With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 5287936
  .TintAndShade = 0
  End With
  Selection.FormatConditions(1).StopIfTrue = False
  Range("H" & i).Select
  Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
  "=AND(H>K,K<>"" "")"
  Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
  With Selection.FormatConditions(1).Font
  .Bold = True
  .Italic = False
  .ThemeColor = xlThemeColorDark1
  .TintAndShade = 0
  End With
  With Selection.FormatConditions(1).Interior
  .PatternColorIndex = xlAutomatic
  .Color = 255
  .TintAndShade = 0
  End With
  Selection.FormatConditions(1).StopIfTrue = False
  Next i
End Sub

 

[Somendra Misra]

@Tom90

Why are doing it through macro? when it can be done through normal conditional formatting by extending the range.

Regards,

 

[Tom90]

Can you explan a bit more

 

[Somendra Misra]

See in below Pic. the box Applies to you can put =$A$1:$A$500. The rule will be applied on all 500 rows.



Regards,

 

[Tom90]

Hi SM,

Thanks I did not know you could do that so I have learned something new

Thanks Tom90