Conditional Formatting using VBA ...continued...

Eloise T · Aug 23, 2017

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

...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.

NARAYANK991 · Aug 24, 2017

Hi ,

Upload your workbook if possible.

Narayan

Eloise T · Aug 25, 2017

NARAYANK991 said:
Hi ,

Upload your workbook if possible.

Narayan

Uploaded. Sanitizing was laborious enough, but knocking it down to under 1Mb was a killer!

Eloise T · Aug 26, 2017

NARAYANK991 said:
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

Eloise T · Aug 26, 2017

NARAYANK991 said:
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"""

NARAYANK991 · Aug 26, 2017

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

shrivallabha · Aug 26, 2017

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

shrivallabha · Aug 26, 2017

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).

Eloise T · Aug 27, 2017

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

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.

shrivallabha · Aug 27, 2017

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).

NARAYANK991 · Aug 27, 2017

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

Eloise T · Aug 27, 2017

shrivallabha said:
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)

shrivallabha · Aug 27, 2017

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)

Eloise T · Aug 27, 2017

shrivallabha said:
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?

NARAYANK991 · Aug 28, 2017

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

shrivallabha · Aug 28, 2017

I am not sure either while I am quite sure the language used is English

.

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.

Eloise T · Aug 28, 2017

I found a link while browsing on-line that I don't quite understand completely but it looks like something there might make sense to you, especially for the APPLIES TO box:

https://stackoverflow.com/questions...ional-formatting-to-a-range-of-cells#21281162

Eloise T · Aug 29, 2017

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.

NARAYANK991 · Aug 29, 2017

Hi ,

See if this works.

Narayan

Eloise T · Aug 29, 2017

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.

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
However, VBA doesn't want to compile the A for the "square root" character (√)(221A)

Any and all help is greatly appreciated!

NARAYANK991 · Aug 30, 2017

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

Eloise T · Aug 30, 2017

NARAYANK991 said:
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?

Chihiro · Aug 30, 2017

Here's link to converter. It also has explanation on hex to decimal calculation.
http://www.binaryhexconverter.com/hex-to-decimal-converter

YasserKhalil · Aug 30, 2017

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

Eloise T · Aug 30, 2017

Chihiro said:
Here's link to converter. It also has explanation on hex to decimal calculation.
http://www.binaryhexconverter.com/hex-to-decimal-converter

Excellent!

Conditional Formatting using VBA ...continued...

Active Member

Excel Ninja

Active Member

Attachments

Active Member

Active Member

Excel Ninja

Attachments

Excel Ninja

Excel Ninja

Attachments

Active Member

Attachments

Excel Ninja

Excel Ninja

Active Member

Excel Ninja

Active Member

Excel Ninja

Excel Ninja

Active Member

Active Member

Attachments

Excel Ninja

Attachments

Active Member

Excel Ninja

Active Member

Excel Ninja

Well-Known Member

Active Member