Conditional Formatting using VBA

Hi I want to write a Macro that will compare data from two cells and then format accordingly.

I don't want to manually set each cell with conditional format because the #of data differs each day, and there are close to 600 values.

I have data in Column B and C everyday, so I want to use something like check the lastrow with value in it.

The Condtional Formatting will go like this

Data starts with cell B3:
For cell B4
If B4>B3, format interior blue
If B4<B3, yellow
If B4=B3, no color

Then it moves on to the next cell B5:
If B5>B4, format interior blue
If B5<B4, yellow
If B5=B4, no color

Then it keeps on doing it until the last cell in the column.

Try.

Code:

---

Sub CondLoop()
Range("B4").Activate
Do Until ActiveCell = ""
Select Case ActiveCell
Case Is < ActiveCell.Offset(-1, 0).Value
ActiveCell.Interior.ColorIndex = 6
Case Is > ActiveCell.Offset(-1, 0).Value
ActiveCell.Interior.ColorIndex = 33
Case Is = ActiveCell.Offset(-1, 0).Value
ActiveCell.Interior.ColorIndex = xlNone
End Select
ActiveCell.Offset(1, 0).Activate
Loop
End Sub

---

Why use VBA, why not just standard CF?

To write a macro that will compare data from two cells and format the cells accordingly, you can use the following steps:


Open the Microsoft Visual Basic for Applications (VBA) editor by pressing Alt + F11.


In the VBA editor, select "Insert" and then "Module" to create a new module.


In the new module, type the following code to define a new subroutine called "FormatCells":

Code:

---

Sub FormatCells()

End Sub

---

To find the last row with data in it, use the "End" and "Down" methods of the Range object:

Code:

---

Sub FormatCells()
    Dim lastRow As Long
    lastRow = Range("B" & Rows.Count).End(xlUp).Row
End Sub

---

To iterate over the cells in column B starting from B3, use a for loop with the "lastRow" variable as the upper bound:

Code:

---

Sub FormatCells()
    Dim lastRow As Long
    lastRow = Range("B" & Rows.Count).End(xlUp).Row
    For i = 3 To lastRow
        ' Compare the values in cells B3, B4, B5, etc. and format the cells accordingly
    Next i
End Sub

---

Inside the for loop, use the "If" statement to compare the values in cells B3, B4, B5, etc. and format the cells accordingly:

Code:

---

Sub FormatCells()
    Dim lastRow As Long
    lastRow = Range("B" & Rows.Count).End(xlUp).Row
    For i = 3 To lastRow
        If Cells(i, 2).Value > Cells(i - 1, 2).Value Then
            ' Format the cell with blue interior
        ElseIf Cells(i, 2).Value < Cells(i - 1, 2).Value Then
            ' Format the cell with yellow interior
        Else
            ' Do not format the cell
        End If
    Next i
End Sub

---

To format the cells with a blue or yellow interior, use the "Interior" property of the Range object:

Code:

---

Sub FormatCells()
    Dim lastRow As Long
    lastRow = Range("B" & Rows.Count).End(xlUp).Row
    For i = 3 To lastRow
        If Cells(i, 2).Value > Cells(i - 1, 2).Value Then
            Cells(i, 2).Interior.Color = vbBlue
        ElseIf Cells(i, 2).Value < Cells(i - 1, 2).Value Then
            Cells(i, 2).Interior.Color = vbYellow
        Else
            ' Do not format the cell
        End If
    Next i
End Sub

---

To run the macro, press F5 or select "Run" and then "Run Sub/UserForm" in the VBA editor. This will execute the code in the "FormatCells" subroutine and apply the formatting to the cells in column B.