Excel 2010 VBA With Setting Cell Colors

Question

I am trying to automate a highlighting process to show that certain rows are complete. This process works well, however at the beginning of my sheet I am trying to make the code create a "Key" for the sheet to show the different meanings of the colors. In the key, the colors White, Green, and Red are used as labels for being Not yet completed (White), completed (green) and Error/Incomplete (Red). Grey is also used for private pipes. I am trying to color the corresponding labels with their color, green as green, red as red, etc. I have made code for this that works for the first line, but on green it does not color the "Green" cell as green (it leaves it white) and on "Red" it colors the cell as green. Here is the code I am using (I apologize it is bad as I am new to coding):

Sub SmartHighlight()
Dim Counter As Integer
Dim i As Integer
Dim j As Integer
Dim k As Range
Dim Chkr As Integer
Chkr = 0
xTitleId = "Smart Highlighter"
MsgBox "This macro analyzes the given pipe data to highlight completed sections."
For Each k In ActiveSheet.UsedRange.Rows
    Counter = 0
    i = 8
    For j = 0 To 3
        If ActiveSheet.Cells(k.Row, i).Value = 0 Then
            Counter = Counter + 1
        End If
        i = i + 1
    Next j
    If ActiveSheet.Cells(k.Row, 1) = "PIPE_ID" Then
        ActiveSheet.Cells(k.Row, 15).Value = "KEY:"
        ActiveSheet.Cells(k.Row, 16).Value = "White"
        ActiveSheet.Cells(k.Row, 17).Value = "Not yet completed."
        ActiveSheet.Cells(k.Row, 18).Value = "Grey"
        k.Cells(k.Row, 18).Interior.ColorIndex = 15
        ActiveSheet.Cells(k.Row, 19).Value = "Private."
    ElseIf ActiveSheet.Cells(k.Row, 1) = "" And Chkr = 0 Then
        ActiveSheet.Cells(k.Row, 16).Value = "Green"
        k.Cells(k.Row, 16).Interior.ColorIndex = 4
        ActiveSheet.Cells(k.Row, 17).Value = "Completed."
        Chkr = Chkr + 1
    ElseIf ActiveSheet.Cells(k.Row, 1) = "" And Chkr = 1 Then
        ActiveSheet.Cells(k.Row, 16).Value = "Red"
        k.Cells(k.Row, 16).Interior.ColorIndex = 3
        ActiveSheet.Cells(k.Row, 17).Value = "Error/Incomplete."
    ElseIf ActiveSheet.Cells(k.Row, 4) = "PRIVATE PIPE" Then
        k.EntireRow.Interior.ColorIndex = 15
    ElseIf Counter <> 4 Then
        k.EntireRow.Interior.ColorIndex = 4
    ElseIf Counter = 4 And ActiveSheet.Cells(k.Row, 14) = "" Then
        k.EntireRow.Interior.ColorIndex = 3
    End If
Next k



End Sub

And here is the output in excel: Click for Image

If anyone can help me correct the coloring error that would be most appreciated. Thanks!

Conditional formatting might be easier for this application... — ERT
Thanks for the response E. Trauger! I unfortunately am trying to reuse this macro in other sheets hence why I am trying to write it with VBA. From what I know there is no way to automatically carry over conditional formatting to all of my other sheets (there are quite many) without defining it again. Ultimately I'm trying to link this macro to my ribbon bar so I can just click it once and move on to the next. — dashton

Tim Williams Tim Williams · Accepted Answer · 2017-07-10T18:04:33

It's unclear why you're switching between ActiveSheet.Cells and k.Cells ?

    ActiveSheet.Cells(k.Row, 18).Value = "Grey"
    k.Cells(k.Row, 18).Interior.ColorIndex = 15

In your code the Range k represent a single row in your sheet's data. You can get a reference to a single cell in that range using Cells(), but you need to be careful to use the correct index(es), otherwise you'll end up with a cell which is not even part of k.

For example, if k is set to row 4 on your sheet:

k.Cells(4).Address()

is D4, but

k.Cells(k.Row,4).Address() 'same as k.Cells(4, 4).Address()

is D7, not D4

k.Cells(1, 4).Address()

would be D4.

Excel 2010 VBA With Setting Cell Colors

2 Answers