Let's paint a Monalisa painting and leave Picasso behind using VBA. Ready, Get Set …
Wait! Wait! Hold your horses. We are just going to use VBA to do conditional formatting and it's easier than a painting that ? shaped bird.
The Generic syntax of VBA Conditional Formatting
If condition than Range(range).Interior.ColorIndex= 1-56
Give your condition check at the condition and then format your range using property .iteriour.colorindex of Range object. The color index has 56 colors. Go play Holi with them and explore which number holds which color.
Now let's understand it with an example
So, this is the scenario, we have a list of random people with their age and age group.
How easy it be if could tell the age group a person by just looking at their names.
Now to do this, I want VBA format Name RED if he is an adult, YELLOW if he is a teenager and GREEN if he is a KID and nothing if the cell is blank.
Sub FormatUsingVBA()
Dim rng As Range
Dim lastRow As Long
lastRow = Cells(Rows.Count, 3).End(xlUp).Row
Set rng = Range("C2:C" & lastRow)For Each cell In rng
If cell.Value2 = "Adult" Then
Range(cell.Address).Offset(0, -2).Interior.ColorIndex = 3
ElseIf cell.Value2 = "KID" Then
Range(cell.Address).Offset(0, -2).Interior.ColorIndex = 4
ElseIf cell.Value2 = "Teenager" Then
Range(cell.Address).Offset(0, -2).Interior.ColorIndex = 6
Else
Range(cell.Address).Offset(0, -2).Interior.ColorIndex = 0
End If
Next cell
End Sub
To run this code right from the sheet, I have drawn a button on the same sheet and assigned this macro to it. Now, whenever you will click the “Format” button your code will run and update name cells with color depending on their age group. How? Let's find out.
Dim rng As RangeDim lastRow As LongThese two lines are variable declarations. rng for Range that holds the age group and lastRow to get last nonblank row number.
lastRow = Cells(Rows.Count, 3).End(xlUp).RowThis line returns last row number in lastRow Variable.
Set rng = Range("C2:C" & lastRow)This line sets the range starting from C2 and to the last row.
It makes your code dynamic. You add new rows to your data and it will detect and will store in new range in rng variable.
For Each cell In rng If cell.Value2 = "Adult" Then Range(cell.Address).Offset(0, -2).Interior.ColorIndex = 3 ElseIf cell.Value2 = "KID" Then Range(cell.Address).Offset(0, -2).Interior.ColorIndex = 4 ElseIf cell.Value2 = "Teenager" Then Range(cell.Address).Offset(0, -2).Interior.ColorIndex = 6 Else Range(cell.Address).Offset(0, -2).Interior.ColorIndex = 0 End If Next cell
This is the main segment.
For Each cell In rng
The first line runs the loop to all cells in your range. If cell.Value2 = "Adult" Then
The next line is a condition check. It checks if the current cell value is Adult or not.
If yes then next line runs, else it skips to next if statement.Range(cell.Address).Offset(0, -2).Interior.ColorIndex = 3
Now if IF condition returns TRUE, then this line sets the cell color to ColorIndex 3 that is for RED.
Similarly following IF statements run and take action as specified.
So yeah, you can conditionally format ranges using VBA. Interior method of Range object controls many other properties for formatting. You should toy with them, it won't do any harm but you will learn definitely. And if you face any difficulty than what I am here for. Ask your questions in the comments section.
Download file
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.
This is not a good solution. Rather use ".FormatConditions.Add Type:=xlExpression,......" then it will work without having to run the macro each time, just like regular excel conditional format.
You are right Anil. However, this solution will add conditional formatting to the conditional formatting list. it will not update the existing formatting if require to do.
This requires you to run the macro everytime you make a change to the key cells.
Is there a way for the macro to run everytime I make a change to the cell itself like conditional formatting works normally?
Peter Allen, have you figured this out yet? I am working through the very same problem...
Hi Jake and Peter,
We can use events here to trigger the subroutine whenever a change is made in the sheets. It is quite easy but important, so wrote a different helping article. Below is the link. Let us know if it doesn't help.
https://www.exceltip.com/events-in-vba/run-macro-if-any-change-made-on-sheet.html