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.
Sub ColorByTactic() Dim icolor As Integer Dim c As Range For Each c In Range("Calendar") Select Case Left(c.Value, 1) Case "1" icolor = 22 Case "2" icolor = 45 Case "3" icolor = 44 Case "4" icolor = 36 Case "5" icolor = 35 Case "6" icolor = 37 Case "7" icolor = 39 Case "8" icolor = 15 Case Else icolor = xlAutomatic End Select c.Interior.ColorIndex = icolor Next c End Sub
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!