Conditional formatting - different cell colour for each year following from user input date?

[Ken Johnson]

Hi Hypatia,

when I replaced that with "Wkt" just to see if it would work, it didn't.<

It does depend on the "Wkt#" and the reason it didn't work was you
probably kept the Number of Characters argument of the "Left" function
equal to 1, so, you have to change that line of code to :

If Left(SheetArray(iRowCounter, iColumnCounter), 3) = "Wkt" Then

for it to work. Notice the 3 ('Wkt" has 3 characters) where previously
there was a 1.

Correct if I'm wrong, if my memory serves me correct, you are not
wanting either the Wkt# or the series of underscores in the following
cell to appear in a new version for use at your workplace.
One way of achieving this is to include a line of code in that version
which makes the font color in those cells the same as the cell interior
color (icolor). This way they're still there, just not visible on
screen or printed page (I assume).

The new code follows:

Public Sub ColorYear()
Dim iRowCounter As Long, iColumnCounter As Long
Dim SheetArray As Variant
SheetArray = Range("A1:H1430")
For iRowCounter = 3 To UBound(SheetArray)
For iColumnCounter = 1 To UBound(SheetArray, 2)

If Left(SheetArray(iRowCounter, iColumnCounter), 1) = "W" Then
iYearIndex = Year(Cells(iRowCounter - 1, iColumnCounter +
1).Value) Mod 4
If iYearIndex = 0 Then Let icolor = 19 '2008,2012,2016
If iYearIndex = 1 Then Let icolor = 40 '2005,2009,2013
If iYearIndex = 2 Then Let icolor = 20 '2006,2010,2014
If iYearIndex = 3 Then Let icolor = 35 '2007,2011,2015

Range(Cells(iRowCounter - 2, iColumnCounter),
Cells(iRowCounter, iColumnCounter + 1)) _
.Interior.ColorIndex = icolor
Range(Cells(iRowCounter, iColumnCounter), Cells(iRowCounter,
iColumnCounter + 1)) _
.Font.ColorIndex = icolor
End If
Next iColumnCounter
Next iRowCounter

End Sub

If you run this code but then you change your mind because you want
them visible again you can change ".Font.ColorIndex = icolor" in the
last line of the loop to ".Font.ColorIndex = 0" then run the code
again.

Let me know how this goes.

Ken Johnson