create a button to do conditional formatting

[kerm007]

hello
i try to do a conditional formating with a button
i have this code but its not right

ub Highlight()
Dim MyRange As Range
'Create range object
Set MyRange = Sheets("Fixtures").Range("B:D")
'Delete previous conditional formats
MyRange.FormatConditions.Delete
'Add first rule
MyRange.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
        Formula1:="=H", Formula2:="=150"
MyRange.FormatConditions(1).Interior.Color = RGB(255, 0, 0)
End Sub

i have number on colum B and F and some are Numbers with an H
i want to put in bold and red the line where its number and H
like 60H

can i do that ?
Thanks

ps: the button i create is on another sheet because when i ran the fixture the button disapear
ty

 

[rlv01]

Sub Highlight()
    Dim MyRange As Range
    Dim FormulaStr As String

    'Create range object
    Set MyRange = Sheets("Fixtures").Range("B:D")

    'Delete previous conditional formats
    MyRange.FormatConditions.Delete

    'Create Formula
    '=AND(RIGHT(B1,1)="H",ISNUMBER(IFERROR(VALUE(LEFT(B1,1)),"")))
    FormulaStr = "=AND(RIGHT(B1,1)=" & """" & "H" & """" & ",ISNUMBER(IFERROR(VALUE(LEFT(B1,1))," & """""" & ")))"
    Debug.Print FormulaStr

    'Add first rule
    MyRange.FormatConditions.Add xlExpression, Formula1:=FormulaStr
    MyRange.FormatConditions(1).Interior.Color = RGB(255, 0, 0)
End Sub

 

[kerm007]

Thanks it work
the only thing i miss is how it can put in the whole line
i change the Set MyRange = Sheets("Fixtures").Range("B:D") for Set MyRange = Sheets("Fixtures").Range("B:F")
also how can the text in the cell be in bold ?
for the rest its perfect TY

 

[rlv01]

I don't know what you mean by "put it in the whole line"

 

[kerm007]

i mean instead of the cell i will give you an example
thanks

 

[rlv01]

Sub Highlight()
    Dim MyRange As Range
    Dim FormulaStr As String
    
    'Create range object
    Set MyRange = ActiveSheet.Range("B:D")            'Sheets("Fixtures").Range("B:D")
    
    'Delete previous conditional formats
    MyRange.FormatConditions.Delete
    
    'Create Formula
    '=AND(RIGHT(B1,1)="H",ISNUMBER(IFERROR(VALUE(LEFT(B1,1)),"")))
    FormulaStr = "=AND(RIGHT($B1,1)=" & """" & "H" & """" & ",ISNUMBER(IFERROR(VALUE(LEFT($B1,1))," & """""" & ")))"
    
    'Add first rule
    With MyRange.FormatConditions.Add(xlExpression, Formula1:=FormulaStr)
        .Interior.Color = RGB(255, 0, 0)
        .Font.Bold = True
        .Font.Color = vbWhite
    End With
    
    FormulaStr = "=AND(RIGHT($C1,1)=" & """" & "H" & """" & ",ISNUMBER(IFERROR(VALUE(LEFT($C1,1))," & """""" & ")))"
    
    'Add 2nd rule
    With MyRange.FormatConditions.Add(xlExpression, Formula1:=FormulaStr)
        .Interior.Color = RGB(255, 0, 0)
        .Font.Bold = True
        .Font.Color = vbWhite
    End With
    
    FormulaStr = "=AND(RIGHT($D1,1)=" & """" & "H" & """" & ",ISNUMBER(IFERROR(VALUE(LEFT($D1,1))," & """""" & ")))"
    
    'Add 3rd rule
    With MyRange.FormatConditions.Add(xlExpression, Formula1:=FormulaStr)
        .Interior.Color = RGB(255, 0, 0)
        .Font.Bold = True
        .Font.Color = vbWhite
    End With
End Sub

If you want to extend the range to B:F (or anything else), I think it is obvious where to change the range in the example. You will also need to add 2 more rules.

 

[kerm007]

hello i try the code and nothing happen but the first code you gave me did the job but for the cell only i think i will leave it like this
Thanks

 

[kerm007]

hello ok it work but only when the number and the H is there on the Left colum B
if the number and the H is on the right side colum F it does nothing
should i add this ?

FormulaStr = "=AND(RIGHT($F1,1)=" & """" & "H" & """" & ",ISNUMBER(IFERROR(VALUE(LEFT($F1,1))," & """""" & ")))"

and if yes where ?
TY

 

[kerm007]

hello never mind i figure it out i forgot part of the code at first then i see the second formulat that need to be change also it work well TY

 

[kerm007]

one less question if i want to add another look for another word call No Game
should i add a FormulaStr and a rules ?
or the rules is enough ?
Thanks