VBA - advanced macro changing color of cells based on values from data

Question

I'm trying to write a code which changes the color of cells in the specified area based on previously defined values (dates). So if the defined date minus actual date is smaller than zero then cells interior color changes to red.

I'm pulling data from area: From row 2 To 160 And column 24 to 33. I'm checking the difference between these dates and the actual date and if its less than zero I want cells in the region: Row 2 To 160 and column 10 to 19 to change color to red.

I wrote a simple code to only test if it works. But the color is changed to red skipping the condition (some values are grater then zero and either way they are red).

Sub niowy()

Worksheets("External").Activate

    For i = 2 To 160
        For j = 24 To 33
            For k = 10 To 19
            If Cells(i, j).Value = "" Then
                Cells(i, j).Select
                Cells(i, k).Select
                ElseIf Cells(i, j).Value - Date > 0 And Cells(i, j).Value - Date < 20 Then
                Cells(i, k).Interior.Color = rgbOrange
                ElseIf Cells(i, j).Value - Date < 0 Then
                Cells(i, k).Interior.Color = rgbRed
            End If
            Next k
        Next j
    Next i
End Sub

If you have any idea to speed up a code a little bit or a different approach I would be grateful for any ideas. Take into consideration I'm just starting learning vba so the code might be pretty messy. I also tested "datediff" function but it failed.

Read [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). But why don't you just use [Conditional Formatting](https://support.office.com/en-us/article/use-formulas-with-conditional-formatting-fed60dfa-1d3f-4e13-9ecb-f1951ff89d7f) for that? That should be much easier. — Pᴇʜ, Jan 08 '19 at 10:27
Thank you for your answer. I will read it soon. I thought it would be much more efficient if appropriate code is applied. Anyway i will try to use conditional formatting in this case and i will let you know how it went. — glizzz, Jan 08 '19 at 12:57

score 0 · Accepted Answer · answered Jan 08 '19 at 10:39

Sub niowy()
Dim target As Range
Dim i As Long
Dim j As Long
With Worksheets("Main")

    For i = 2 To 160
        For j = 24 To 33

            If Val(.Cells(i, j)) = 0 Then
            Else
               Set target = .Range("J" & i & ":t" & i)
               Select Case (.Cells(i, j).Value - Date)
                   Case 1 To 20
                        target.Interior.Color = rgbOrange
                   Case Is < 0
                        target.Interior.Color = rgbRed

            End Select
           End If
        Next j
    Next i
    End With
End Sub

What is "t" in this code? I'm getting error "subscript out of range". — glizzz, Jan 08 '19 at 12:52

VBA - advanced macro changing color of cells based on values from data

1 Answers1