How to create an Excel macro to Conditional Format

August 20, 2017 at 19:12:27
Specs: Windows 7
I need a macro so that it will conditionally format the cells of four different columns.
I would like the formula to run until it sees a blank row for those columns that I want to condition; however, I'm not sure that is really necessary.

I receive a regular report for each of my customers but the number of rows will vary based on the amount of business that particular customer has. Each customer has their own tab (worksheet) in the workbook, except for tab #1 which is where I document formula information. For whatever reason (I believe due to appending rows from the regular reports), my current CFs seem to change the range on their own and I have to go back into the Conditional Formatting to “reset” them back to what they need to be.

Each report has the same column headings which use Rows 1 and 2, so the CFs need to start in Row 3 (see below). I plan to put a button in the “Formula Info” tab (worksheet #1) that I can use to initiate the macro.

Currently the CFs are as follows:
Use this formula to determine which cells to format: =$L3="LEN ≠ 9"
=$L$3:$L$5003 in Bold red

Format only cells that contain Specific text containing: √
=$K$3:$K$5003,$M$3:$M$5003 in Bold red

Format only cells that contain Specific text containing: D70
=$C$3:$C$5003 in black on red background with vertical fill effects.

Format only cells that contain Specific text containing: M75
=$C$3:$C$5003 in black on magenta background with vertical fill effects.

Format only cells that contain Specific text containing: PNL80
=$C$3:$C$5003 in black on blue background with vertical fill effects.

message edited by 4Dale2


See More: How to create an Excel macro to Conditional Format

Reply ↓  Report •

#1
August 21, 2017 at 12:24:58
First, a posting tip:

You have told us that you "need" a macro and you've told us your requirements, but you haven't really asked for any help. Since we are all volunteers here, giving freely of our time, it would make us feel more generous if you actually asked for some assistance or thanked us in advance for any suggestions, as opposed to just telling us what you need.

That said, here is what I just did:

I started the Macro Recorder.
I selected C3:C10
I applied CF to that range based on the "PNL80" requirement above.
I opened the VBA Editor and saw the following (it's a partial paste of what was recorded, but you'll get the idea):

Sub Macro10()
'
' Macro10 Macro
'

'
    Range("C3:C10").Select
    Selection.FormatConditions.Delete
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=C3=""PNL80"""
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .Pattern = xlPatternLinearGradient
        .Gradient.Degree = 0
        .Gradient.ColorStops.Clear
    End With
    With Selection
         -----etc.-----
End Sub

I replaced all of the Selection related words with Range("C3:C5003") as shown below:

Sub Macro10()
'
' Macro10 Macro
'

'
    Range("C3:C5003").FormatConditions.Delete
    Range("C3:C5003").FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=C3=""PNL80"""
    Range("C3:C5003").FormatConditions(Range("C3:C5003").FormatConditions.Count).SetFirstPriority
    With Range("C3:C5003").FormatConditions(1).Interior
        .Pattern = xlPatternLinearGradient
        .Gradient.Degree = 0
        .Gradient.ColorStops.Clear
    End With
    With Range("C3:C5003")
         -----etc.-----
End Sub

When I run the macro, it deletes the existing CF and applies the rule.

If this works for you, I can show you how to make the Range length variable. However, I would need clarification on this requirement:

I would like the formula to run until it sees a blank row for those columns that I want to condition.

There are 2 different situations that you could be talking about and the coding for each is very different.

1 - A blank row that has more data below it, e.g.

     C            
3   Data
4   Data
5   Data
6      
7   Data
8   Data
etc.

2 - A blank row that signifies the end of the data in that column, e.g.

     C            
3   Data
4   Data
5   Data
6      
7
8
etc.

Which do you have?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Reply ↓  Report •

#2
August 28, 2017 at 13:14:03
First of all, my experience with VBA will fit in a thimble.
I am asking for assistance to create a macro so that it will Conditionally Format the cells of four different columns, C, F, L, and K & M.
I would like the formula to run until it runs out of data entirely. It may encounter a few individual blank rows, and on rare occasion, 2 or 3 consecutive blank rows followed by more rows of data.
I receive a regular reports for each of my customers. Each customer has their own tab (worksheet in the workbook). The number of rows in each tab varies based on the amount of business that particular customer has.
Tab #1 and Tab #2, are exceptions, which is where I document formula information and have a TEST tab.
For whatever reason (I believe due to appending rows to the regular reports), my current CFs seem to mysteriously change the range (Applied to box) on their own and I have to go back into the Conditional Formatting to “reset” them back to what they need to be manually. Needless to say a huge chore.
Each tab has the same layout and column headings which use Rows 1 and 2, so the CFs need to start in Row 3 (see below). As an aside, I plan to put a button in the “Formula Info” tab (worksheet #1) that I can use to run the macro.
The "Next" tab contains the correct Conditional Formats.

Currently the CFs are as follows:
Format only cells that contain Specific text containing: D70
Range (Applies to) =$C$3:$C$5003 in black on red background with vertical gradient fill effects. I am having difficulty finding the specific code necessary for the vertical gradient fill effects. Example:

Format only cells that contain Specific text containing: M75
Range (Applies to) =$C$3:$C$5003 in black on magenta background with vertical gradient fill effects. I am having difficulty finding the specific code necessary for the vertical gradient fill effects. Example:

Format only cells that contain Specific text containing: PNL80
Range (Applies to) =$C$3:$C$5003 in black on blue background with vertical gradient fill effects. I am having difficulty finding the specific code necessary for the vertical gradient fill effects. Example:
Use this formula to determine which cells to format:
=OR($F3="REBILLED",$F3="REPAID",$F3="PAID")
Range (Applies to) =$F$3:$F$5003 in magenta on lime green background.
This seems to be working ok as there is no vertical gradient.
Example:

Use this formula to determine which cells to format:
=$L3="LEN ≠ 9"
Range (Applies to) =$L$3:$L$5003 in Bold red. (The problem here is VBA doesn't like the text "≠")(not equals to character)
Format only cells that contain Specific text containing: √
Range (Applies to) =$K$3:$K$5003,$M$3:$M$5003 in Bold red (The problem here is VBA doesn't like the text "√")(square root character)

I pasted some examples next to Example: but they didn't transfer.

The following is the code I have cobbled together:

Sub Conditional_Format_Reset()
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
         
'       " N O R M A L "
'        If ws.Name <> "Formula Info" And ws.Name <> "TEST" Then

'      A L L   B U T   O N E   T A B   O F F   U S I N G   N A M E S   D U R I N G   T E S T I N G
       If ws.Name <> "Formula Info" And ws.Name <> "TEST" And ws.Name <> "CW Ass." And ws.Name <> "DB Co." And ws.Name <> "CO Springs" And ws.Name <> "J. McC. Co." And ws.Name <> "R. B. Co." And ws.Name <> "M. S. Co." And ws.Name <> "J.M. Co." And ws.Name <> "Next" Then
'      T H I S   T A B   I S   B E I N G   A F F E C T E D   B Y   V B A
'                                   And ws.Name <> "J Co."

'       If ws.Number <> 1 And ws.Number <> 2 And ws.Number <> 3 And ws.Number <> 4 And ws.Number <> 5 And ws.Number <> 6 And ws.Number <> 7 And ws.Number <> 8 And ws.Number <> 9 And ws.Number <> 10 And ws.Number <> 11 Then
            
            If ws.Cells(Rows.Count, 3).End(xlUp).Row > 2 Then
                lLastRow = ws.Cells(Rows.Count, 3).End(xlUp).Row - 2
                
'               Below line tells which arrays to apply change(s) to.  (Rows.Count, 3) tells in which row to start.
'                With ws.Range("C3:C" & lLastRow & "K3:K" & lLastRow & "L3:L" & lLastRow & "M3:M" & lLastRow)
'                With ws.Range("F3:F" & lLastRow)
'                With ws.Range("L3:L" & lLastRow)
'                With ws.Range("K3:K" & lLastRow & "M3:M" & lLastRow)

                
'                   Remove previous Conditional Formats
'                D O E S   T H I S   N E E D   T O   B E   H E R E   O R   I N   A L L   S E G M E N T S ?
'                    .FormatConditions.Delete    'INVALID OR UNQUALIFIED REFERENCE


'         > > > This VBA is intended to reset the Conditional Formats for cell rows 3 to 5003 in the Columns C, K, L, and M.
'                   The above "With" statement defines the 4 arrays where to apply change(s).  (Rows.Count, 3) tells in which row to start.
 
'               U S I N G   "Formulas" > "Defined Names" > "Name Manager" (SearchFor70, SearchFor75, SearchFor80,
'                                                                          SearchFor84, SearchFor85, SearchFor90)
 
'70             This doesn't work YET since it's using a formula and not "Format cells containing Specific text..."
                With ws.Range("C3:C" & lLastRow)               'Intended Range =$C$3:$C$5003
                    .FormatConditions.Delete
                    .FormatConditions.Add Type:=xlExpression, Formula1:="=OR(ISNUMBER(SEARCH(SearchFor70,$C3)))"
                    .FormatConditions(1).Font.ColorIndex = 1        'Black
                    .FormatConditions(1).Interior.ColorIndex = 3    'Red
                    
'               Handler for stopping code
                    For i = 1 To .FormatConditions.Count
                        .FormatConditions(i).StopIfTrue = False
                    Next
                End With
                 
'75             This doesn't work YET since it's using a formula and not "Format cells containing Specific text..."
                With ws.Range("C3:C" & lLastRow)               'Intended Range =$C$3:$C$5003
                    .FormatConditions.Delete
                    .FormatConditions.Add Type:=xlExpression, Formula1:="=OR(ISNUMBER(SEARCH(SearchFor75,$C3)))"
                    .FormatConditions(1).Font.ColorIndex = 1        'Black
                    .FormatConditions(1).Interior.ColorIndex = 7    'Magenta
                    
'               Handler for stopping code
                    For i = 1 To .FormatConditions.Count
                        .FormatConditions(i).StopIfTrue = False
                    Next
                End With


'80             This doesn't work YET since it's using a formula and not "Format cells containing Specific text..."
                With ws.Range("C3:C" & lLastRow)                   'Intended Range =$C$3:$C$5003
                    .FormatConditions.Delete
                    .FormatConditions.Add Type:=xlExpression, Formula1:="=OR(ISNUMBER(SEARCH(SearchFor80,$C3)))"
                    .FormatConditions(1).Font.ColorIndex = 1        'Black
                    .FormatConditions(1).Interior.ColorIndex = 33   'Lt. Blue
                    
'                   Handler for stopping code
                        For i = 1 To .FormatConditions.Count
                            .FormatConditions(i).StopIfTrue = False
                        Next
                End With
                        
'84             This doesn't work YET since it's using a formula and not "Format cells containing Specific text..."
                With ws.Range("C3:C" & lLastRow)               'Intended Range =$C$3:$C$5003
                    .FormatConditions.Delete
                    .FormatConditions.Add Type:=xlExpression, Formula1:="=OR(ISNUMBER(SEARCH(SearchFor84,$C3)))"
                    .FormatConditions(1).Font.ColorIndex = 1        'Black
                    .FormatConditions(1).Interior.ColorIndex = 15   'Brown
                    
'                   Handler for stopping code
                        For i = 1 To .FormatConditions.Count
                            .FormatConditions(i).StopIfTrue = False
                        Next
                End With
                        
'85             This doesn't work YET since it's using a formula and not "Format cells containing Specific text..."
                With ws.Range("C3:C" & lLastRow)               'Intended Range =$C$3:$C$5003
                    .FormatConditions.Delete
                    .FormatConditions.Add Type:=xlExpression, Formula1:="=OR(ISNUMBER(SEARCH(SearchFor85,$C3)))"
                    .FormatConditions(1).Font.ColorIndex = 1        'Black
                    .FormatConditions(1).Interior.ColorIndex = 33   'Violet
                    
'                   Handler for stopping code
                        For i = 1 To .FormatConditions.Count
                            .FormatConditions(i).StopIfTrue = False
                        Next
                End With

'90              This doesn't work YET since it's using a formula and not "Format cells containing Specific text..."
                 With ws.Range("C3:C" & lLastRow)               'Intended Range =$C$3:$C$5003
                    .FormatConditions.Delete
                    .FormatConditions.Add Type:=xlExpression, Formula1:="=OR(ISNUMBER(SEARCH(SearchFor90,$C3)))"
                    .FormatConditions(1).Font.Bold = True           'Bold
                    .FormatConditions(1).Font.ColorIndex = 14       'Green
                    .FormatConditions(1).Interior.ColorIndex = 6    'Yellow
                    
'       T R Y I N G   T O   F I G U R E    O U T   G R A D I E N T
'                    .FormatConditions.Pattern = xlPatternLinearGradient
'                    .FormatConditions.Gradient.Degree = 0
'                    .FormatConditions.Gradient.ColorStops.Clear

                    
'                   Handler for stopping code
                        For i = 1 To .FormatConditions.Count
                            .FormatConditions(i).StopIfTrue = False
                        Next
                 End With
                 
'WORKING         This DOES work since it's using a formula and is looking for and finding exact text.
                With ws.Range("F3:F" & lLastRow)               'Intended Range =$F$3:$F$5003
                    .FormatConditions.Delete
                    .FormatConditions.Add Type:=xlExpression, Formula1:="=OR($F3=""REBILLED"",$F3=""REPAID"",$F3=""PAID"")"
                    .FormatConditions(1).Font.Bold = True           'Bold
                    .FormatConditions(1).Font.ColorIndex = 7        'Magenta
                    .FormatConditions(1).Interior.ColorIndex = 4    'Green
                    
'                   Handler for stopping code
                        For i = 1 To .FormatConditions.Count
                            .FormatConditions(i).StopIfTrue = False
                        Next
                End With


'Not=to chr     This doesn't work YET for several reasons, one of which is "not = to" character id.

                With ws.Range("L3:L" & lLastRow)               'Intended Range =$L$3:$L$5003
                   .FormatConditions.Delete
'               Below is a workaround as VBA editor can't intrepret the "not equal to" character.
                   .FormatConditions.Add Type:=xlExpression, Formula1:="=$L3=""LEN "" & ChrW(8800) & "" 9"""
'                   .FormatConditions.Add Type:=xlExpression, Formula1:="=$L3=""LEN "" & Chr(61) & "" 9"""
                   .FormatConditions(1).Font.Bold = True           'Bold
                   .FormatConditions(1).Font.ColorIndex = 3        'Red
                   .FormatConditions(1).Interior.ColorIndex = 2    'White
                    
'                  Handler for stopping code
                       For i = 1 To .FormatConditions.Count
                           .FormatConditions(i).StopIfTrue = False
                       Next
                End With
  

'Sq. rt. chr.   This doesn't work YET for several reasons, one of which is "sq. rt." character character id.
'               Additionally the arrays K3:K and M3:M are incorrectly overlaying column L.
                With ws.Range("K3:K" & lLastRow, "M3:M" & lLastRow)  'Intended Range =$K$3:$K$5003,$M$3:$M$5003
                    .FormatConditions.Delete
'               Below is a workaround as VBA editor can't intrepret the "square root" character; looks like a check mark.
                    .FormatConditions.Add Type:=xlExpression, Formula1:="=$L3=""LEN "" & ChrW(221) & "" 9"""
'                   .FormatConditions.Add Type:=xlExpression, Formula1:="=$L3=""LEN "" & Chr(88) & "" 9"""
                    .FormatConditions(1).Font.Bold = True           'Bold
                    .FormatConditions(1).Font.ColorIndex = 3        'Red
                    .FormatConditions(1).Interior.ColorIndex = 2    'White

'                   Handler for stopping code
                        For i = 1 To .FormatConditions.Count
                            .FormatConditions(i).StopIfTrue = False
                        Next
                End With
            End If
        End If
    Next
End Sub

message edited by 4Dale2


Reply ↓  Report •
Related Solutions


Ask Question