[Solved] Conditional formatting or Macros and VBA coding

[Craige Harper]

Have spreadsheet were columns D thru I. Column G is tested to determine what color the letters will in columns D thru I. Certain rows that are totals will be skipped. Should I be using Conditional Formatting or Macros and VBA Coding. ??. I have a total of 11 groups of Columns D thru I.

Thanks for your help

 

[Yousuf Shovon]

Should I be using Conditional Formatting or Macros and VBA Coding. ?

Hello Craige,

Welcome to ExcelDemy Forum!

To answer your question, Conditional Formatting is a better choice than VBA. Though in this case, I would apply Conditional Formatting in VBA by defining criteria for each conditional format.

Here is the VBA code:

Sub ApplyConditionalFormatting()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim rng As Range
    Dim cell As Range

    Set ws = ThisWorkbook.Worksheets("Sheet1")
    lastRow = ws.Cells(ws.Rows.Count, "G").End(xlUp).Row

    Set rng = ws.Range("D4:I" & lastRow)
    rng.FormatConditions.Delete

    For Each cell In ws.Range("G4:G" & lastRow)
    
        If InStr(1, cell.Formula, "SUM(", vbTextCompare) = 0 Then
            If IsNumeric(cell.Value) Then
                If cell.Value = 0 Or cell.Value > 30 Or cell.Value = "DNQ" Then
                    ApplyColor cell, rng, RGB(0, 0, 0) ' Black
                ElseIf cell.Value > 0 And cell.Value < 6 Then
                    ApplyColor cell, rng, RGB(255, 0, 0) ' Red
                ElseIf cell.Value > 5 And cell.Value < 11 Then
                    ApplyColor cell, rng, RGB(0, 0, 255) ' Blue
                ElseIf cell.Value > 10 And cell.Value < 16 Then
                    ApplyColor cell, rng, RGB(255, 192, 203) ' Pink
                ElseIf cell.Value > 15 And cell.Value < 21 Then
                    ApplyColor cell, rng, RGB(210, 180, 140) ' Tan
                ElseIf cell.Value > 20 And cell.Value < 26 Then
                    ApplyColor cell, rng, RGB(255, 0, 0) ' Red (Duplicate of 2nd condition)
                ElseIf cell.Value > 25 And cell.Value < 31 Then
                    ApplyColor cell, rng, RGB(165, 42, 42) ' Brown
                End If
            End If
        End If
    Next cell
End Sub

Sub ApplyColor(cell As Range, rng As Range, color As Long)
    
    rng.Rows(cell.Row - rng.Rows(1).Row + 1).Font.color = color
End Sub

I have attached the workbook for practice. Try and add further conditions in the loop. Let me know if it works for you.

Regards.