Conditional Formatting using VBA

[KarNeedsHelp]

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.

[omnibuster]

Try.

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

[Bob Phillips]

Why use VBA, why not just standard CF?

[Grade4.2]

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":


Sub FormatCells()

End Sub

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


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:


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:


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:


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.