Improve Color Index Conditional Formatting Macro

  • Hi there!


    Here is some VBA code I'm using to apply fill-color formatting to a calendar I've developed, based on exmaples in Dave's book and on this forum.



    1) Can anyone help modify this code to ALSO highlight the first 4 characters (LEFT(c.value,4)) of the entry to match the fill-color (thereby rendering that first part of the text "invisible", as it will just blend into the color fill behind it)?


    2) Can anyone help add code that would recognize repeated data and change the font color so that the cell contents become "invisible" in cases where they match exactly to the cell on the left?


    I've attached an example worksheet which shows what my macros is doing and what I'd like it to do. I have seen macros on this forum which will match the font color in one group of cells to another "legend" set of cells where matches occur, but this gets more complicated when you only want to see the first unique entry in a range containing matches, and I have a lot of variations of tactic # and promoted $ price.


    Any help is MUCH appreciated!

  • Re: Improve Color Index Conditional Formatting Macro


    Quote from JRSmith729


    1) Can anyone help modify this code to ALSO highlight the first 4 characters (LEFT(c.value,4)) of the entry to match the fill-color (thereby rendering that first part of the text "invisible", as it will just blend into the color fill behind it)?


    Code
    With ActiveCell.Characters(Start:=1, Length:=4).Font
            .ColorIndex = iColor ' same as other listed in Select Case
        End With


    Quote from JRSmith729


    2) Can anyone help add code that would recognize repeated data and change the font color so that the cell contents become "invisible" in cases where they match exactly to the cell on the left?


  • Re: Improve Color Index Conditional Formatting Macro


    WOW!


    I love this forum! I logged in just 1 minute after your reply was posted! Thank you so much for your help!


    I have one question about the second code sample you provided. In your comments you warn against large sets of cells and blanks. I have about 7500 cells in the range "Calendar" (52 weeks x about 150 rows). These cells are populated with a VLOOKUP returning "" when no match is found, but every cell in the "Calendar" range has either a formula result of "" or a hard-coded text/numeric value. With this information:


    1) Will this approach for populating my spreadsheet create any problems for your code, or should I be alright?


    2) I'm always a bit intimidated when I see code with the Integer (i = # to ##) type of logic because I'm only really comfortable modifying existing code samples. I'm not yet fluent enough to write code "from scratch" on my own and this type of logic is above my level of comprehension right now. Will I need to input a Range("B2").Select argument for ALL 7500 cells in the target area, or just 52 for the full width of one row (allowing the code to somehow pass from there onto the next row)? Can I just enter Range("Calendar").Select and capture all cells in my (dynamic) Named Range?


    3) In general, what does the whole "Integer (i - # to ##)" expression actually DO? Why repeat the code 100 times? Why not 10 or 68 times?


    I really appreciate your help. And patience!

  • Re: Improve Color Index Conditional Formatting Macro


    I actually caught your previous code without the "-4142" line. Should I discard the old sample? I noticed the change, but couldn't tell you what it means.

  • Re: Improve Color Index Conditional Formatting Macro


    Quote from JRSmith729

    WOW!
    I have one question about the second code sample you provided. In your comments you warn against large sets of cells and blanks. I have about 7500 cells in the range "Calendar" (52 weeks x about 150 rows). These cells are populated with a VLOOKUP returning "" when no match is found, but every cell in the "Calendar" range has either a formula result of "" or a hard-coded text/numeric value. With this information:


    1) Will this approach for populating my spreadsheet create any problems for your code, or should I be alright?


    If the formula returns "", the loop may end early, as it's checking the activecell.value and telling it to exit the sub. You can either delete the line with Exit Sub; or you can leave it intact and then have your formula return "0". If the latter, from the menu, you'd go to Tools -> Options and choose to hide all 0's.


    Quote from JRSmith729


    2) I'm always a bit intimidated when I see code with the Integer (i = # to ##) type of logic because I'm only really comfortable modifying existing code samples. I'm not yet fluent enough to write code "from scratch" on my own and this type of logic is above my level of comprehension right now. Will I need to input a Range("B2").Select argument for ALL 7500 cells in the target area, or just 52 for the full width of one row (allowing the code to somehow pass from there onto the next row)? Can I just enter Range("Calendar").Select and capture all cells in my (dynamic) Named Range?


    The Range("B2").Select refers to the very first cell that you choose to compare. You should not need to change this cell value more than once. It will be the first cell in your column that you want the conditional changes applied. The macro then goes down to the next row and compares those two values, then repeats itself.


    I haven't looked at your worksheet, so I don't know what goes where.


    Quote from JRSmith729


    3) In general, what does the whole "Integer (i - # to ##)" expression actually DO? Why repeat the code 100 times? Why not 10 or 68 times?


    The For i = 1 to 75 (or whatever the last integer was) is just telling the loop to repeat itself this many times. You say your target has 7500 cells in any given column that you want these changes applied to. So, you might say For i = 1 to 10000 (leaving whatever room for any extra cells added at a later date to compensate for this).

  • Re: Improve Color Index Conditional Formatting Macro


    Quote from JRSmith729

    I actually caught your previous code without the "-4142" line. Should I discard the old sample? I noticed the change, but couldn't tell you what it means.


    Yes, discard the old sample. I changed the code because I hadn't thought of "what if" there is no formatting applied and the two values are the same. The -4142 refers to no-formatting to the cell; the value for the font here is 2 (white). So if it comes across two values that are the same and there is no formatting (color) applied to the cell, the font-color thus becomes white and "invisible". BTW, another method to make the text "invisible" would be to go to the cell's formatting and change it to ;;; . This would make it that way no matter what the cell's color.

    Code
    Selection.NumberFormat = ";;;"


    And to make it viewable again:

    Code
    Selection.NumberFormat = "General"
  • Re: Improve Color Index Conditional Formatting Macro


    OK. Now I've looked at your workbook. You'd have the macro, say, select the one range, go down as many spaces as needed, then repeat itself.


    There's probably a simpler way to do this, but I don't want to get too wrapped up in your situation, which won't do you much good since you won't learn anything from it then.

  • Re: Improve Color Index Conditional Formatting Macro


    I'm feeling really lame right now. You've given me all this great material to get me to my end goal, but in terms of implementing the code I'm still back at square one. I've tried numerous variations and placements of your line of code:


    Code
    With ActiveCell.Characters(Start:=1, Length:=4).Font 
        .ColorIndex = iColor ' same as other listed in Select Case
    End With


    How would the syntax look within my original full code sample?


    I've tried placing the With / End With expression after each "Case" and I've tried just listing it once below the End Select expression, but I haven't found the proper syntax. Any suggestions? Again, any help is much appreciated.

  • Re: Improve Color Index Conditional Formatting Macro


    Hi norie!


    Thanks for the suggestion, but it's still not working the way I've imlemented the code. Here's how I modified the (end) of my original code:


    Code
    End Select
            c.Interior.ColorIndex = icolor
            c.Characters(Start:=1, Length:=2).Font.ColorIndex = icolor
        Next c
    End Sub


    The result was that the fill updated properly, but ALL text was changed to match the fill coloring (so none of the text was "visible" anymore, it all just blends into the fill coloring), not just the first 2 characters in each cell.


    When I tried using the With/End With expression like this, nothing happened to the text, but the macro ran successfully and colored the fill as expected:


    Code
    End Select
            c.Interior.ColorIndex = icolor
            With c.Characters(Start:=1, Length:=2).Font.ColorIndex = icolor
            End With
        Next c
    End Sub


    What am I missing to get both the FILL color, AND the FONT color of the first 2 characters of the text to MATCH? Do I need to place the With/End With expression WITHIN each individual case argument in the original code sample at the beginning of this post?


    Also, I am getting a compile error (End If without block If) when I run my version of the recommended code from MrkFrrl for changing the font color based on left column similar values. Here's what I tried:


    Code
    Sub HideRepetitiveText()
        Dim i As Integer
            For i = 1 To 200
            If ActiveCell.Value = "" Then Exit Sub
            If ActiveCell.Value = ActiveCell.Offset(0, -1).Range("A1").Value Then ActiveCell.Font.ColorIndex = ActiveCell.Interior.ColorIndex
            End If
        End If
        ActiveCell.Offset(1, 0).Range("A1").Select
    Next i
    End Sub


    I don't follow why the "ActiveCell.Offset(#,#).Range(name).XXXXX" are referencing "A1." Is that just a place holder within the code to name the cell to the left "A1" is the code really looking at cell A1? Does that reference need to be changed to suit my situation (my target area begins at column F, so intuitively the first "offset(0,-1)" should be column E)?


    I hope I'm making sense.

  • Re: Improve Color Index Conditional Formatting Macro


    Hi there!


    For future reference I managed to correct this macro compile error on my own as follows to get the desired result:



    Consider my questions posted here as closed. I'll start a new (shorter) thread with any further issues.

  • Re: Improve Color Index Conditional Formatting Macro


    Quote from JRSmith729

    When I tried using the With/End With expression like this, nothing happened to the text, but the macro ran successfully and colored the fill as expected...


    What am I missing to get both the FILL color, AND the FONT color of the first 2 characters of the text to MATCH? Do I need to place the With/End With expression WITHIN each individual case argument in the original code sample at the beginning of this post?.


    The way I showed you should work. I don't know why it wouldn't for you. Maybe it has something to do with the way you set up your code.


    Quote from JRSmith729

    Also, I am getting a compile error (End If without block If) when I run my version of the recommended code from MrkFrrl for changing the font color based on left column similar values. Here's what I tried:


    Code
    Sub HideRepetitiveText()
        Dim i As Integer
            For i = 1 To 200
            If ActiveCell.Value = "" Then Exit Sub
            If ActiveCell.Value = ActiveCell.Offset(0, -1).Range("A1").Value Then ActiveCell.Font.ColorIndex = ActiveCell.Interior.ColorIndex
            End If
        End If
        ActiveCell.Offset(1, 0).Range("A1").Select
    Next i
    End Sub


    That's because you altered what I showed you. You're running the If-statement straght into the Then statement, and also then showing an End If at the end. As an example, you can have an If-Then statement as one of these two ways, but not both:


    Code
    If ActiveCell.Value = 7 then ActiveCell.Value = 8
    ' or like this
    If ActiveCell.Value = 7 then
    ActiveCell.Value = 8
    End If


    In your example, you're trying to do both, which is causing your error.

    Quote from JRSmith729


    I don't follow why the "ActiveCell.Offset(#,#).Range(name).XXXXX" are referencing "A1." Is that just a place holder within the code to name the cell to the left "A1" is the code really looking at cell A1? Does that reference need to be changed to suit my situation (my target area begins at column F, so intuitively the first "offset(0,-1)" should be column E)?


    I hope I'm making sense.


    You don't need to change the A1 reference value in the activecell.offset method; it's essentially a place-holder.

  • Re: Improve Color Index Conditional Formatting Macro


    Thanks!


    I appreciate you taking the time to educate me on the syntax. I've definitely learned a lot from you on this project.


    I started a new thread to try to resolve just the issue I've been having with the "With / End With" lines of code you provided to handle the task of matching the font-color of the frist few text characters to the color-fill of the cell. I've tried lots of different things but none of them have the desired effect.


    I've only had two results with my efforts: the macro either 1) ignores the code and does nothing to the text, or 2) it changes ALL text in the cell!


    You can take a look at the new thread if you like. Just search with the keywords "With Under Case Select" in your search.


    Again, you've been a huge help. Thank you.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!