Conditional formatting macro does not work past 6 columns

[Egan3208]

My macro won't work past 6 columns.

I found a macro that formats a cell's color based on the RBG values in other cells. I have modified the original code so that it fits my spreadsheet.

Original code:

Private Sub Worksheet_Change(ByVal Target As Range)Dim rng As Range, cell As Range
Dim i As Long


Set rng = Intersect(Target, Range("A:C"))
If Not rng Is Nothing Then
    On Error Resume Next
    For Each cell In Target.Columns(1).Cells
        If Application.CountA(Range("A" & cell.Row & ":C" & cell.Row)) < 3 Or _
            Application.Count(Range("A" & cell.Row & ":C" & cell.Row)) < 3 Then GoTo next_row
        Cells(cell.Row, "D").Interior.Color = _
            RGB(Cells(cell.Row, "A").Value, Cells(cell.Row, "B").Value, Cells(cell.Row, "C").Value)
next_row:
    Next cell
End If
End Sub

My Code:

Public Sub Worksheet_Change(ByVal Target As Range)Dim rng As Range, cell As Range
Dim i As Long


Set rng = Intersect(Target, Range("5:7"))
If Not rng Is Nothing Then
    On Error Resume Next
    For Each cell In Target.Rows(1).Cells
        If Application.CountA(Range(cell.Column & "5:" & cell.Column & "7")) < 3 Or _
            Application.Count(Range(cell.Column & "5:" & cell.Column & "7")) < 3 Then GoTo next_col
        Cells("8", cell.Column).Interior.Color = _
            RGB(Cells("5", cell.Column).Value, Cells("6", cell.Column).Value, Cells("7", cell.Column).Value)
next_col:
    Next cell
End If
End Sub

The problem is that it only works on the first 6 columns. (I'm new to mrexcel.com, so please forgive the formatting) Below you will see a sample from my spreadsheet. The macro is supposed to format the cell color in the 4th row (I know the macro says row 8, I excluded the first 4 rows form my sample) based on the RBG values in the 3 rows directly above.

[the "===" represent cells that have been colored black by the macro. April 1st and April 8th have also been formatted by the macro.]

[TABLE="width: 724"]
<tbody>[TR]
[TD].[/TD]
[TD].[/TD]
[TD].[/TD]
[TD].[/TD]
[TD="align: right"]112[/TD]
[TD="align: right"]75[/TD]
[TD="align: right"]37[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD].[/TD]
[TD].[/TD]
[TD].[/TD]
[TD].[/TD]
[TD="align: right"]48[/TD]
[TD="align: right"]91[/TD]
[TD="align: right"]133[/TD]
[TD="align: right"]176[/TD]
[TD="align: right"]176[/TD]
[TD="align: right"]176[/TD]
[TD="align: right"]176[/TD]
[/TR]
[TR]
[TD].[/TD]
[TD].[/TD]
[TD].[/TD]
[TD].[/TD]
[TD="align: right"]160[/TD]
[TD="align: right"]187[/TD]
[TD="align: right"]213[/TD]
[TD="align: right"]240[/TD]
[TD="align: right"]187[/TD]
[TD="align: right"]133[/TD]
[TD="align: right"]80[/TD]
[/TR]
[TR]
[TD]===[/TD]
[TD]===[/TD]
[TD]===[/TD]
[TD]===[/TD]
[TD="align: right"]4/1/2016[/TD]
[TD="align: right"]4/8/2016[/TD]
[TD="align: right"]4/15/2016[/TD]
[TD="align: right"]4/22/2016[/TD]
[TD="align: right"]4/29/2016[/TD]
[TD="align: right"]5/6/2016[/TD]
[TD="align: right"]5/13/2016[/TD]
[/TR]
[TR]
[TD].[/TD]
[TD].[/TD]
[TD]Class[/TD]
[TD]L C R[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD]302L[/TD]
[TD="align: right"]301.9[/TD]
[TD="align: right"]302[/TD]
[TD="align: right"]-1[/TD]
[TD="align: right"]952[/TD]
[TD="align: right"]975[/TD]
[TD="align: right"]975[/TD]
[TD="align: right"]1003[/TD]
[TD="align: right"]1040[/TD]
[TD="align: right"]1068[/TD]
[TD="align: right"]1096[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD="align: right"]302[/TD]
[TD="align: right"]302[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1664[/TD]
[TD="align: right"]1686[/TD]
[TD="align: right"]1686[/TD]
[TD="align: right"]1714[/TD]
[TD="align: right"]1752[/TD]
[TD="align: right"]1779[/TD]
[TD="align: right"]1808[/TD]
[/TR]
[TR]
[TD]R[/TD]
[TD="align: right"]302.1[/TD]
[TD="align: right"]302[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2601[/TD]
[TD="align: right"]2623[/TD]
[TD="align: right"]2623[/TD]
[TD="align: right"]2652[/TD]
[TD="align: right"]2689[/TD]
[TD="align: right"]2716[/TD]
[TD="align: right"]2744[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]307L[/TD]
[TD="align: right"]306.9[/TD]
[TD="align: right"]307[/TD]
[TD="align: right"]-1[/TD]
[TD]
[/TD]
[TD="align: right"]2321[/TD]
[TD="align: right"]2357[/TD]
[TD="align: right"]2357[/TD]
[TD="align: right"]2386[/TD]
[TD="align: right"]2423[/TD]
[TD="align: right"]2463[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD="align: right"]307[/TD]
[TD="align: right"]307[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2693[/TD]
[TD="align: right"]2732[/TD]
[TD="align: right"]2764[/TD]
[TD="align: right"]2768[/TD]
[TD="align: right"]2797[/TD]
[TD="align: right"]2834[/TD]
[TD="align: right"]2872[/TD]
[/TR]
[TR]
[TD]R[/TD]
[TD="align: right"]307.1[/TD]
[TD="align: right"]307[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2283[/TD]
[TD="align: right"]2321[/TD]
[TD="align: right"]2353[/TD]
[TD="align: right"]2357[/TD]
[TD="align: right"]2388[/TD]
[TD="align: right"]2423[/TD]
[TD="align: right"]2463[/TD]
[/TR]
</tbody>[/TABLE]

If I delete column A, April 15th will be formatted correctly. So it would seem that it is only formatting the first 6 columns.

My first inclination was that there was some kind of limit I needed to change in the options menu, but I was unable to find any such field. I've asked other colleagues, and did my best to find the answer on the googlenet, but no such luck.

Any help would be great. I'm using Excel 2013.

Thanks!

 

[V_Malkoti]

My guess is that your problem lies in this If statement.

If Application.CountA(Range(cell.Column & "5:" & cell.Column & "7")) < 3 Or _
            Application.Count(Range(cell.Column & "5:" & cell.Column & "7")) < 3 Then GoTo next_col

Suppose I modify value in cell C6. In that case variable cell = C6, which means cell.column = 3.
Now because of this, the If statement starts checking number of non-blank cells in row 35 to 37.

If Application.CountA(Range("3" & "5:" & "3" & "7")) < 3 Or _
            Application.Count(Range("3" & "5:" & "3" & "7")) < 3 Then GoTo next_col

which is

If Application.CountA(Range("35:37")) < 3 Or _
            Application.Count(Range("35:37")) < 3 Then GoTo next_col

As you move past column F, code starts checking rows far below in the spreadsheet. For example, if you modify cell G6, then code checks number of non-blank cells in range("75:77"). It looks like your spreadsheet data ends around row 70 which is why column 7 doesn't have highlighted cell.
Is this way of checking blank cells intentional?

Or, if you are actually trying to check three cells in rows 5:7 of the column the modified cells, then you need to change your If statement.

If Application.CountA(Range(Cells(5, cell.Column), Cells(7, cell.Column))) < 3 Or _
            Application.Count(Range(Cells(5, cell.Column), Cells(7, cell.Column))) < 3 Then GoTo next_col

 

[Egan3208]

Yes, my spreadsheet does end at row 72. Great catch!

That fixed the issue 100%. Now, even the first 4 columns have stopped formatting to black.

Thank you very much!