highlight numbers

[kerm007]

hello
in a round robin match up to avoid certain match up i did that :

 Set rng = Range("B5:F700")

    With rng
        .FormatConditions.Add Type:=xlExpression, Formula1:= _
            "=AND(COUNTIF($B5:$F5,54)>0,COUNTIF($B5:$F5,9)>0)"
        .FormatConditions(.FormatConditions.Count).SetFirstPriority
        With .FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .Color = RGB(0, 128, 0)
            .TintAndShade = 0
        End With
        
        .FormatConditions(1).StopIfTrue = False
        .FormatConditions.Add Type:=xlExpression, Formula1:= _
            "=AND(COUNTIF($B5:$F5,3)>0,COUNTIF($B5:$F5,9)>0)"
        .FormatConditions(.FormatConditions.Count).SetFirstPriority
        With .FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .Color = RGB(0, 128, 0)
            .TintAndShade = 0
        End With
        
        .FormatConditions(1).StopIfTrue = False
        .FormatConditions.Add Type:=xlExpression, Formula1:= _
            "=AND(COUNTIF($B5:$F5,5)>0,COUNTIF($B5:$F5,6)>0)"
        .FormatConditions(.FormatConditions.Count).SetFirstPriority
        With .FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .Color = RGB(0, 128, 0)
            .TintAndShade = 0
        End With
        
        .FormatConditions(1).StopIfTrue = False
        .FormatConditions.Add Type:=xlExpression, Formula1:= _
            "=AND(COUNTIF($B5:$G5,8)>0,COUNTIF($B5:$G5,10)>0)"
        .FormatConditions(.FormatConditions.Count).SetFirstPriority
        With .FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .Color = RGB(0, 128, 0)
            .TintAndShade = 0
        End With
        
        .FormatConditions.Add Type:=xlExpression, Formula1:= _
            "=AND(COUNTIF($B5:$F5,8)>0,COUNTIF($B5:$F5,10H)>0)"
        .FormatConditions(.FormatConditions.Count).SetFirstPriority
        With .FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .Color = RGB(0, 128, 0)
            .TintAndShade = 0
        End With
        
        .FormatConditions(1).StopIfTrue = False
    End With

End Sub

it work for certain but since i add this one :

.FormatConditions.Add Type:=xlExpression, Formula1:= _

            "=AND(COUNTIF($B5:$F5,8)>0,COUNTIF($B5:$F5,10H)>0)"

        .FormatConditions(.FormatConditions.Count).SetFirstPriority

        With .FormatConditions(1).Interior

            .PatternColorIndex = xlAutomatic

            .Color = RGB(0, 128, 0)

            .TintAndShade = 0

        End With

it give me an error is it because the number have an H to it ?
Thanks

 

[kerm007]

i test out and if there no H to the number it work
how can i make it work with the H ?
Thanks

 

[kerm007]

anyone ?
Thanks

 

[MARK858]

If the 10H is a text string in a cell then it needs to be in double quotes, if it is a cell reference then it needs to be H10

 

[kerm007]

hello i tries it like this :

Sub MyFormat()

    Dim rng As Range
    Set rng = Range("B5:F700")

    With rng
        .FormatConditions.Add Type:=xlExpression, Formula1:= _
            "=AND(COUNTIF($B5:$F5,54)>0,COUNTIF($B5:$F5,9)>0)"
        .FormatConditions(.FormatConditions.Count).SetFirstPriority
        With .FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .Color = RGB(0, 128, 0)
            .TintAndShade = 0
        End With
        
        .FormatConditions(1).StopIfTrue = False
        .FormatConditions.Add Type:=xlExpression, Formula1:= _
            "=AND(COUNTIF($B5:$F5,3)>0,COUNTIF($B5:$F5,9)>0)"
        .FormatConditions(.FormatConditions.Count).SetFirstPriority
        With .FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .Color = RGB(0, 128, 0)
            .TintAndShade = 0
        End With
        
        .FormatConditions(1).StopIfTrue = False
        .FormatConditions.Add Type:=xlExpression, Formula1:= _
            "=AND(COUNTIF($B5:$F5,5)>0,COUNTIF($B5:$F5,6)>0)"
        .FormatConditions(.FormatConditions.Count).SetFirstPriority
        With .FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .Color = RGB(0, 128, 0)
            .TintAndShade = 0
        End With
        
        .FormatConditions.Add Type:=xlExpression, Formula1:= _
            "=AND(COUNTIF($B5:$G5,8)>0,COUNTIF($B5:$G5,"10H")>0)"
        .FormatConditions(.FormatConditions.Count).SetFirstPriority
        With .FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .Color = RGB(0, 128, 0)
            .TintAndShade = 0
        End With
        
        
        .FormatConditions(1).StopIfTrue = False
    End With

End Sub

it failed on the 10H

Thanks

 

[MARK858]

You need double quotes as I stated
""10H""

 

[kerm007]

Thanks it work

 

[MARK858]

You're welcome