VBA - Conditional Formatting

gd6noob

Board Regular
Joined
Oct 20, 2017
Messages
145
Office Version
  1. 2016
Platform
  1. Windows
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.

VBA Code:
    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
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,116
Office Version
  1. 365
Platform
  1. Windows
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.
Excel Formula:
=$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

Board Regular
Joined
Oct 20, 2017
Messages
145
Office Version
  1. 2016
Platform
  1. Windows
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.
Excel Formula:
"=$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
VBA Code:
"=$N2=""High"""
for wildcard and also nothing.

VBA Code:
    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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,116
Office Version
  1. 365
Platform
  1. Windows
This little test seemed to work for me:
VBA Code:
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

Well-known Member
Joined
May 16, 2017
Messages
1,149
Office Version
  1. 365
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

Another variant:

VBA Code:
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

Board Regular
Joined
Oct 20, 2017
Messages
145
Office Version
  1. 2016
Platform
  1. Windows
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.
 

Forum statistics

Threads
1,148,018
Messages
5,744,355
Members
423,863
Latest member
teehexcel

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top