Conditional formatting

[James__S]

Hi

I am using the following rules in Conditional formatting
format Only Cells that Contain
1. Cell Value Greater than =206 will turn RED
2. Cell Value Less than or equal to =205 will turn GREEN

now if i have nothing in the range the Cell is still RED....how do i have the cell White if there is no value in the cell

Thanks

 

[jtakw]

Hi,

I'm getting the other way around, staying GREEN, anyway try changing "Less than or equal to" 205, if there won't be negative numbers or zeros, between >=1 and <=205

 

[James__S]

Hi

Thanks

i used Between and it worked..thanks

 

[jtakw]

You're welcome, the reason I believe is that Excel assigns a 0 value to a blank cell for logic comparison, therefore, blank cell <=205 returns TRUE.

 

[Dossfm0q]

May contain Space

 

[jtakw]

May contain Space

Book1
	A	B	C
1		FALSE	FALSE
2		FALSE	FALSE
3		TRUE	TRUE
4	0	TRUE	TRUE
Sheet8

Cell Formulas
Range	Formula
B1	=A1=0
B2	=A2=0
B3	=A3=0
B4	=A4=0
C1	=A1<=205
C2	=A2<=205
C3	=A3<=205
C4	=A4<=205
A1	=""
A2	=" "

 

[Dossfm0q]

Good day All

Try

Sub AdConditions()


Dim Rng As Range, CondRng As Range
    Dim CondClr As Variant
    Dim Adrs As String
    Dim C As Long
    
   Set CondRng = Range("A1:H10") 'Conditions range
   'Set CondRng = Range("A1:A20") 'Conditions range for some in Column (A)
   For Each Rng In CondRng
   With Rng
   Adrs = Rng.Address
   CondFrml = Split("=AND(" & Adrs & "<>""""," & Adrs & "<=205);=AND(" & Adrs & "<>""""," & Adrs & ">=206)", ";") 'Conditions Formula
   CondClr = Split("38400;255", ";") ' color
    .FormatConditions.Delete
    For C = LBound(CondFrml) To UBound(CondFrml)
            .FormatConditions.Add Type:=xlExpression, Formula1:=CondFrml(C) 'Conditions Formula
            .FormatConditions(.FormatConditions.Count).SetFirstPriority
            With .FormatConditions(1).Interior
                .PatternColorIndex = xlAutomatic
                .Color = CondClr(C) ' color
            End With
        .FormatConditions(1).StopIfTrue = False
    Next
    End With
    Next
    
End Sub