ColorIndex Issues

[BrianJN1]

I had an issue trying to colour format a calendar of events.

I initially started with conditional formatting but it seemed that after a certain point the formats were not being obeyed. I then began to wonder if cells had a numeric limit about the number of times they could be addressed.

An internet search suggested a 3 (or 4) limitation.

There were many places which offered things that I felt to be unwieldly, more to the point, beyond my immediate comprehension.

One place did offer some solace, and there I chose to lift out and modify one line to address my means:
Range("A" & i).Interior.ColorIndex = n

Part of my code, again modified, is:

…..
For c = 1 To ColNo.
For d = 1 To RowNo
    If Cells(d, c).Value = "Criterion" Or Left(Cells(d, c).Value, 10) = "Criterion" Then
   For a = 0 To 1
    Cells(d, c - a).Interior.ColorIndex = 34
    Next
……..

The last FOR loop ensured that the cell holding the target was coloured but also so was the one to its left as that was a date.

I did at last come up with my desired result.

I’d be interested in how others may have fared in trying to overcome that “3/4 conditional formatting limitation”.

 

[mole999]

3 / 4 limitation basically removed from excel 2007 onwards, as is the way it is developed on the cells