1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. 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...

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

Discussion in 'VBA Macros' started by Eloise T, Aug 23, 2017.

  1. Eloise T

    Eloise T Active Member

    Messages:
    656
    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 (vb):

      .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 (vb):

      .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: Aug 23, 2017
  2. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    15,924
    Hi ,

    Upload your workbook if possible.

    Narayan
  3. Eloise T

    Eloise T Active Member

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

    Attached Files:

  4. Eloise T

    Eloise T Active Member

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

    Code (vb):


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

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

     
  5. Eloise T

    Eloise T Active Member

    Messages:
    656
    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 (vb):

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

    NARAYANK991 Excel Ninja

    Messages:
    15,924
    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

    Attached Files:

    Thomas Kuriakose likes this.
  7. shrivallabha

    shrivallabha Excel Ninja

    Messages:
    1,787
    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 (vb):
                    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
  8. shrivallabha

    shrivallabha Excel Ninja

    Messages:
    1,787
    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).

    Attached Files:

  9. Eloise T

    Eloise T Active Member

    Messages:
    656
    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 (vb):

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

    Attached Files:

    Last edited: Aug 27, 2017
  10. shrivallabha

    shrivallabha Excel Ninja

    Messages:
    1,787
    1. You need to modify on below lines:
    Code (vb):
        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).
  11. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    15,924
    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
  12. Eloise T

    Eloise T Active Member

    Messages:
    656
    1.
    To be clear, replace these two lines:
    Code (vb):

    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 (vb):

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

    shrivallabha Excel Ninja

    Messages:
    1,787
    Nah, the code will be of 3 lines post modification as below.
    Code (vb):
    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)
  14. Eloise T

    Eloise T Active Member

    Messages:
    656
    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?
  15. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    15,924
    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
  16. shrivallabha

    shrivallabha Excel Ninja

    Messages:
    1,787
    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 (vb):
    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.
  17. Eloise T

    Eloise T Active Member

    Messages:
    656
  18. Eloise T

    Eloise T Active Member

    Messages:
    656
    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.

    Attached Files:

  19. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    15,924
    Hi ,

    See if this works.

    Narayan

    Attached Files:

  20. Eloise T

    Eloise T Active Member

    Messages:
    656
    Narayan,

    WOW! Nice! Great progress! Thank you!

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

    Code (vb):

      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 (vb):

      .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: Aug 29, 2017
  21. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    15,924
    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
  22. Eloise T

    Eloise T Active Member

    Messages:
    656
    Thank you. Where'd you find the conversion table?
  23. Chihiro

    Chihiro Excel Ninja

    Messages:
    3,914
    Tim Hanson and Eloise T like this.
  24. YasserKhalil

    YasserKhalil Active Member

    Messages:
    774
    This UDF may help you too
    Code (vb):
    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
    Tim Hanson likes this.
  25. Eloise T

    Eloise T Active Member

    Messages:
    656

Share This Page