VBA - Conditional Formatting

[gd6noob]

I have a piece of code that I cant figure out.

basically I want rows in the select range (A to N) to be highlighted if the cell in column N contains "High". "Medium" or Low". My code only works on cells in column N.

    Dim lLR As Long
    lLR = Range("N" & Rows.Count).End(xlUp).Row 'Last Row Found here!
    
        With Range("A2:N" & lLR) 'Last Row used here!
            .Select
            .FormatConditions.Delete
            .FormatConditions.Add Type:=xlTextString, String:="High", TextOperator:=xlContains
            .FormatConditions(1).Interior.Color = RGB(255, 199, 206)
            .FormatConditions(1).Font.Color = RGB(156, 0, 6)
            .FormatConditions.Add Type:=xlTextString, String:="Medium", TextOperator:=xlContains
            .FormatConditions(2).Interior.Color = RGB(255, 235, 156)
            .FormatConditions(2).Font.Color = RGB(156, 0, 6)
            .FormatConditions.Add Type:=xlTextString, String:="Low", TextOperator:=xlContains
            .FormatConditions(3).Interior.Color = RGB(198, 239, 206)
            .FormatConditions(3).Font.Color = RGB(156, 0, 6)
        End With
        Range("P1:Q1").Interior.Color = 65535
        Range("A1:Q1").Font.Bold = True

 

[Joe4]

I don't think you can do it that way, because you do not want each cell to look at the value in itself, but you want them to all look at column N in that row. So I think you need to use the formula option of Conditional Formatting.
To do it without VBA, I would use the "Use a formula to determine which cells to format" option.

So for the first one ("High"), I would first select all the cells I want to apply it to, say "A2:N100", then write the formula as it applies to the first cell in your range, i.e.

=$N2="High"

You need the "$" in front of the column reference, because you want to lock that down, because we want ALL columns to look at column N.
We do not put it in front of the row reference because we want that to "float" (so A3:N3 will look at N3, A4:N4 will look at N4, etc).

After entering that formula, you would pick your desired color.
You can then repeat the steps for the other two conditions.

Now, I know you want VBA code, and you can get this by turning on the Macro Recorder and recording yourself performing those steps manually.
You can then clean up the code a bit (to get rid of "Selects" and such, and to make the range you apply it to dynamic, like you did in your original code).

 

[gd6noob]

I don't think you can do it that way, because you do not want each cell to look at the value in itself, but you want them to all look at column N in that row. So I think you need to use the formula option of Conditional Formatting.
To do it without VBA, I would use the "Use a formula to determine which cells to format" option.

So for the first one ("High"), I would first select all the cells I want to apply it to, say "A2:N100", then write the formula as it applies to the first cell in your range, i.e.

"=$N2=""High*"""

You need the "$" in front of the column reference, because you want to lock that down, because we want ALL columns to look at column N.
We do not put it in front of the row reference because we want that to "float" (so A3:N3 will look at N3, A4:N4 will look at N4, etc).

After entering that formula, you would pick your desired color.
You can then repeat the steps for the other two conditions.

Now, I know you want VBA code, and you can get this by turning on the Macro Recorder and recording yourself performing those steps manually.
You can then clean up the code a bit (to get rid of "Selects" and such, and to make the range you apply it to dynamic, like you did in your original code).

This is what the macro recorder programed but it didnt highlight anything. also tried using

"=$N2=""High"""

for wildcard and also nothing.

    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=$N2=""High"""
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 255
        .TintAndShade = 0
    End With

 

[Joe4]

This little test seemed to work for me:

Sub MyConditionalFormat()

    Dim lLR As Long
    lLR = Range("N" & Rows.Count).End(xlUp).Row 'Last Row Found here!
    
    With Range("A2:N" & lLR) 'Last Row used here!
        .FormatConditions.Add Type:=xlExpression, Formula1:="=$N2=""High"""
        .FormatConditions(1).Interior.Color = 255
    End With
        
End Sub

Once you get that working, they you can start building out the rest.

 

[rlv01]

Another variant:

Sub SetFormatConditions()
    Dim FCRange As Range
    Dim FormulaStr As String
    Dim I As Long, Criteria As Variant, CriteriaColor As Variant

    Criteria = Array("High", "Medium", "Low")
    CriteriaColor = Array(RGB(255, 199, 206), RGB(255, 235, 156), RGB(198, 239, 206))    'H,M,L

    With ActiveSheet
        Set FCRange = .Range("A2:N" & .Range("N" & .Rows.Count).End(xlUp).Row)
        With FCRange.FormatConditions
            .Delete    'It is important to clear existing format conditions each time the macro is run
            For I = 0 To 2                            'H,M,L
                FormulaStr = "=$N2=" & """" & Criteria(I) & """"
                With .Add(Type:=xlExpression, Formula1:=FormulaStr)
                    .StopIfTrue = True
                    .Interior.Color = CriteriaColor(I)
                    .Font.Color = RGB(156, 0, 6)
                End With
            Next I
        End With
    End With
End Sub

 

[gd6noob]

Not sure why but I tried both those and it doesnt work. maybe my workbook is messed up? I gave up working on it... Thank you @Joe4 and @rlv01 for the help.

 

[Joe4]

I now have another question.

Please start a new thread for a new question.