• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Conditional Formatting using VBA ...continued...

Eloise T

Active Member
I have been trying to create (cobble together) a VBA that will reset the 25 Conditional Formats in my 20-tab (currently) workbook so that when the Conditional Formats decide to have a mind of their own, I can simply click on a macro-energized button and everything is back to normal.

21 of the 25 Conditional Formats are similar in part to the following:

Cell Value contains: PNL80
upload_2017-8-23_12-53-14.png
...which allows for other "stuff" to be in the cell besides PNL80...it doesn't have to be only PNL80.

The following example, which implements a formula, includes all the possibilities I need which includes PNL80. Unfortunately, since it's a formula and not simply looking for specific text, it never finds M80, nor P80, nor PNL80.

Code:
  .FormatConditions.Add Type:=xlExpression, Formula1:="=OR($C3=""M80"",$C3=""P80"",$C3=""PNL80"")"
  .FormatConditions(1).Font.ColorIndex = 1       'Black
  .FormatConditions(1).Interior.ColorIndex = 6    'Light Blue   Range =$C$3:$C$5003
On the other hand, the following example DOES work since all that's in the cell is REBILLED, or REPAID, or PAID. ...so it works since it's exact.
Code:
  .FormatConditions.Add Type:=xlExpression, Formula1:="=OR($F3=""REBILLED"",$F3=""REPAID"",$F3=""PAID"")"
  .FormatConditions(1).Font.ColorIndex = 7       'Magenta
   .FormatConditions(1).Interior.ColorIndex = 50  'Green      Range =$F$3:$F$5003

In the first example, PNL80 et. al. will never be exact. How do I change the VBA code from "exact" to simply "find text" as the first picture shows?

Thank you in advance.
 
Last edited:
Hi ,

Upload your workbook if possible.

Narayan
Since uploading the file, I've found two lines (really 4) lines of code VBA doesn't like:

Code:
    .FormatConditions(1).Font.ColorIndex = "Bold"  'Bold
     .FormatConditions(1).Font.ColorIndex = 14      'Green
     

    .FormatConditions(1).Font.ColorIndex = "Bold"  'Bold
     .FormatConditions(1).Font.ColorIndex = 7       'Magenta
 
Hi ,

Upload your workbook if possible.

Narayan
Also, one of the following lines need to be implemented as VBA doesn't "see" ≠ (not equal) in =$L3="LEN ≠ 9"
but I'm not too sure either works as is...

Code:
.FormatConditions.Add Type:=xlExpression, Formula1:="=$L3=""LEN "" & Chr(63) & "" 9"""
.FormatConditions.Add Type:=xlExpression, Formula1:="=$L3=""LEN "" & ChrW(8800) & "" 9"""
 
Hi ,

I am doing the job for the first rule ; you can follow the same principle for all the others.

Define a named range to cater to the search expression ; I have defined the named range SearchFor , and in the Refers To box , I have entered :

={"D70","E70","KDL70","LC70","LC-70","M70","PNC70","PNL70","PNR70"}

The CF rule is now :

=OR(ISNUMBER(SEARCH(SearchFor,$C3)))

This is the rule that will have to be implemented in VBA ; this I have done.

There is still one problem , which is that when you run the macro , the CF rule is being added but is not being applied. I have read about this on the net , but I don't have the time at present to go through it in detail.

What I have seen is that if you click on Conditional Formatting , edit the rule and then press OK without making any changes , the rule is applied.

If you can check it out , please do so , otherwise I will come back on this in the next few days.

Narayan
 

Attachments

  • Chandoo - Conditional Format Reset.xlsm
    769.8 KB · Views: 6
Just curious about CF implementation and I had some time on hand so tried few things. Here's what I think about the CF thing.

1. One should clear old rules before re-applying the old rules.

2. VBA by default sets "StopIfTrue" checkbox to true and the rules appear to be coming with checkbox set to true. Your original file shows them to be in unchecked state. I tried reading this post but don't think I understood what it meant.
https://support.office.com/en-us/ar...ecedence-063cde21-516e-45ca-83f5-8e8126076249

3. Sequence of implementation shall be carefully written in VBA. First written rule remains at the top. This may become significant in case of conflicting rules.

Following is a piece of code which is sample and answers part of your original question about text.
Code:
                With ws.[C3:M3].Resize(ws.Cells(Rows.Count, 3).End(xlUp).Row - 2)
                    '\\ Remove previous conditional formats
                    .FormatConditions.Delete

                    '\\ First condition using contains
                    .FormatConditions.Add Type:=xlTextString, String:="D70", TextOperator:=xlContains
                    .FormatConditions(1).Interior.ColorIndex = 3    'Red
                   
                    .FormatConditions.Add Type:=xlTextString, String:="M75", TextOperator:=xlContains
                    .FormatConditions(1).Interior.ColorIndex = 7    'Magenta
               
                    '\\ At the end just add handler for stopping code
                    For i = 1 To .FormatConditions.Count
                        .FormatConditions(i).StopIfTrue = False
                    Next
                End With
Test above code on a backup so that you don't mess anything up. In my opinion, Narayan's approach is better i.e. using formula based conditional formatting as you will be dealing with too many conditional formats.

You will have to do some trial and fail if you want to implement variety of those conditions through VBA. As a starting point, have a read of:
https://msdn.microsoft.com/en-us/vba/excel-vba/articles/formatconditions-add-method-excel

And the enumeration which are more than one (besides xlExpression which you've used):
https://msdn.microsoft.com/en-us/vba/excel-vba/articles/xlformatconditiontype-enumeration-excel
 
As continuation to above post (just so that it doesn't appear lengthy)...

In Excel 2016, if I turn macro recorder on for conditional formatting then I observed following behavior when "adding a condition":
1. Highlight cells rules - Records
2. Top/Bottom rules - Records
3. Data Bars / Color Scales / Icon Sets - Records
4. Through New Rule
4.1. Format all cells based on their values - Records
4.2. Format only cells that contain - Records
4.3. Format only top or bottom ranked values - Records
4.4. Format only values that are above or below average - Records
4.5. Format only unique or duplicate values
4.6. Use a formula to determine which cells to format - Doesn't Record

So it just doesn't record for one item of which you know the syntax. See if the same applies to Excel 2010 or not. And if it works then you can build your own code without having to refer online documentation by using macro recorder for most part.

I am posting the sample codes recorded just for reference (and I can assure you that they serve no real world purpose as they are just there with one intent to see if the code was getting recorded or not).
 

Attachments

  • cf_trials.xlsm
    18.5 KB · Views: 4
Updated Excel VBA file as well as Name Ranges with Narayan's suggestions .

The last tab, "Next", contains the Conditional Formats exactly as they need to be for all tabs except the "Format Info" tab and "TEST" tab which have been "exceptioned-out" by the VBA formula...see immediately below (in picture):

' " N O R M A L "
If ws.Name <> "Formula Info" And ws.Name <> "TEST" Then


upload_2017-8-26_23-33-6.png
What is the correct syntax and commands for the following lines that VBA doesn't like?

Code:
.FormatConditions(1).Font.ColorIndex = "Bold"  'Bold
.FormatConditions(1).Font.ColorIndex = 14      'Green

Thank you in advance for your help, Narayan and Shrivallabha, and any other contributors.
 

Attachments

  • Chandoo - Conditional Format Reset.xlsm
    773.9 KB · Views: 5
Last edited:
1. You need to modify on below lines:
Code:
    lLastRow = ws.Cells(Rows.Count, 3).End(xlUp).Row - 2
    With ws.Range("C3:C" & lLastRow & ",K3:K" & lLastRow & ",L3:L" & lLastRow & ",M3:M" & lLastRow)

2. For second case, use macro recorder and see what it gives (on a backup).
 
Hi ,

For the font highlighting , I think the syntax should be :

.FormatConditions(1).Font.Bold = True

For the other one , the syntax seems to be correct.

Narayan
 
1. You need to modify on below lines:
Code:
    lLastRow = ws.Cells(Rows.Count, 3).End(xlUp).Row - 2
    With ws.Range("C3:C" & lLastRow & ",K3:K" & lLastRow & ",L3:L" & lLastRow & ",M3:M" & lLastRow)

2. For second case, use macro recorder and see what it gives (on a backup).

1.
To be clear, replace these two lines:
Code:
If ws.Cells(Rows.Count, 3).End(xlUp).Row > 2 Then
With ws.[C3:M3].Resize(ws.Cells(Rows.Count, 3).End(xlUp).Row - 2)

...with these two lines, correct?
Code:
  lLastRow = ws.Cells(Rows.Count, 3).End(xlUp).Row - 2
  With ws.Range("C3:C" & lLastRow & ",K3:K" & lLastRow & ",L3:L" & lLastRow & ",M3:M" & lLastRow)
 
Nah, the code will be of 3 lines post modification as below.
Code:
If ws.Cells(Rows.Count, 3).End(xlUp).Row > 2 Then
    lLastRow = ws.Cells(Rows.Count, 3).End(xlUp).Row - 2
    With ws.Range("C3:C" & lLastRow & ",K3:K" & lLastRow & ",L3:L" & lLastRow & ",M3:M" & lLastRow)
 
Nah, the code will be of 3 lines post modification as below.
Code:
If ws.Cells(Rows.Count, 3).End(xlUp).Row > 2 Then
    lLastRow = ws.Cells(Rows.Count, 3).End(xlUp).Row - 2
    With ws.Range("C3:C" & lLastRow & ",K3:K" & lLastRow & ",L3:L" & lLastRow & ",M3:M" & lLastRow)
OK. I fixed it.

I'm still reading over the "Stop If True" link you provided....and wondering why the "Stop If True" is "lighting up" on the new CFs?
 
Hi ,

I am not too sure that the STOP IF TRUE checkbox does anything , at least on a simple example that I tried out.

Enter the numbers 1 through 10 in any column range , say F3:F12.

Select the range , and enter the following 2 rules :

=$F3 > 6 ----- RED

=ISODD($F3) ----- GREEN

Try all the possible combinations of the 2 checkboxes being checked / unchecked , as well as the order of the two rules , placing them in the order given here , and inverting the order , as follows :

=ISODD($F3) ----- GREEN

=$F3 > 6 ----- RED

These are two non-mutually exclusive rules ; you can try the same with mutually exclusive rules , such as :

=$F3 > 6 ----- RED

=$F3 < 5 ----- GREEN

Narayan
 
I am not sure either while I am quite sure the language used is English :confused:.

Just to maintain the way it appears normally i.e. without VBA, I have provided couple of code lines in post #7.
Code:
For i = 1 To .FormatConditions.Count
    .FormatConditions(i).StopIfTrue = False
Next
Which should be placed at the end of conditional formatting code to remove the ticks.
 
I am uploading the current code.
Some works, some does not.
I tried to document it as best as I can.
When you ALT + F8, Run, it only work on the J Co. tab.
It appears that the biggest problem is the

.FormatConditions.Delete

line which seems to be deleting as fast as the code creates CFs.
Once the .FormatConditions.Delete is figured out (correct placement),
the rest "may" be able to be tweaked.
Only about 3 CFs that are created are remaining in CF list.
 

Attachments

  • Chandoo - Conditional Format Reset.xlsm
    804.4 KB · Views: 5
Narayan,

WOW! Nice! Great progress! Thank you!

1. Since it is commented out, you found the code for "Stop If True" was unnecessary?

Code:
  For i = 1 To .FormatConditions.Count
        .FormatConditions(i).StopIfTrue = False
  Next

2.
upload_2017-8-29_14-6-52.png
I found "stuff" like the 3 lines of code below that "looks" like it could be altered and used. Unfortunately, I haven't been able to find a "www.excelfunctions.net" site for VBA that would explain how.

Code:
  .FormatConditions.Pattern = xlPatternLinearGradient

  .FormatConditions.Gradient.Degree = 0

  .FormatConditions.Gradient.ColorStops.Clear
3. I believe I've found the correct Character/ASCII code for the "square root" character (√)(221A) and the "not equal to" character (≠)(2260) at:
the Insert tab --> Symbols --> Symbol upload_2017-8-29_15-14-12.png
However, VBA doesn't want to compile the A for the "square root" character (√)(221A)

Any and all help is greatly appreciated!
 
Last edited:
Hi ,

221A is actually the hexadecimal representation ; the corresponding decimal value would be 8730.

However , if you wish to use hexadecimal values in VBA , you can use them by prefixing the value by &H , as in &H221A

Narayan
 
Hi ,

221A is actually the hexadecimal representation ; the corresponding decimal value would be 8730.

However , if you wish to use hexadecimal values in VBA , you can use them by prefixing the value by &H , as in &H221A

Narayan
Thank you. Where'd you find the conversion table?
 
This UDF may help you too
Code:
Function HexToDec(hex As String) As Double
    Dim j              As Variant
    Dim hexArray()      As Double
    Dim i              As Long
    Dim k              As Long
    Dim n              As Long

    n = Len(hex)
    k = -1
    ReDim hexArray(1 To n)
   
    For i = n To 1 Step -1
        j = Mid(hex, i, 1)
        k = k + 1
        Select Case j
            Case 0 To 9
                hexArray(i) = j * 16 ^ (k)
            Case Is = "A"
                hexArray(i) = 10 * 16 ^ (k)
            Case Is = "B"
                hexArray(i) = 11 * 16 ^ (k)
            Case Is = "C"
                hexArray(i) = 12 * 16 ^ (k)
            Case Is = "D"
                hexArray(i) = 13 * 16 ^ (k)
            Case Is = "E"
                hexArray(i) = 14 * 16 ^ (k)
            Case Is = "F"
                hexArray(i) = 15 * 16 ^ (k)
        End Select
    Next i
   
    HexToDec = Application.WorksheetFunction.Sum(hexArray)
End Function
 
Back
Top