Conditional formatting

[kamalm]

I have set conditional formatting at both of my column BA and BB. The cells will be filled in color if the values are in the range.

Columns("BA:BA").Select
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
        Formula1:="=0.921", Formula2:="=0.931"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 5296274
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    Columns("BB:BB").Select
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
        Formula1:="=0.069", Formula2:="=0.079"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 5296274
        .TintAndShade = 0
    End With

What code I have to use to make the cells that are not in the range to be filled with other color ?

 

[spiralrain]

The same code should work, just change
Columns("BA:BA").Select

to the range of cells/columns you want to change, and change the color number to the new color you want them.

 

[Yongle]

Try this which assumes that all values in both columns are always positive

What's happening
- clear prior CF
- apply CF colour2 to all cells in column containing values
- apply CF colour1 to all cells falling inside the designated range of vaues

Sub ConditionalFormattngOutsideRange()
    Dim c1 As String:       [COLOR=#ff0000]c1[/COLOR] = 5296274
    Dim c2 As String:       [COLOR=#000080]c2[/COLOR] = 99999999
    Columns("BA").Select
    With Selection
        .FormatConditions.Delete
        
        .FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, Formula1:="=0"
        .FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
        .FormatConditions(1).Interior.Color = [COLOR=#000080]c2[/COLOR]
        
        .FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, Formula1:="=0.921", Formula2:="=0.931"
        .FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
        .FormatConditions(1).Interior.Color = [COLOR=#ff0000]c1[/COLOR]
    End With
        
    Columns("BB").Select
    With Selection
        .FormatConditions.Delete
        
        .FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, Formula1:="=0"
        .FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
        .FormatConditions(1).Interior.Color = [COLOR=#000080]c2[/COLOR]
        
        .FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, Formula1:="=0.069", Formula2:="=0.079"
        .FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
        .FormatConditions(1).Interior.Color = [COLOR=#ff0000]c1[/COLOR]
      
    End With
End Sub