How to change row color in Excel VBA based on value

How to change row color in Excel VBA based on value

Excel is a potent tool that can easily handle tons of data. A beneficial improvement to increase your user experience is to change cell color based on their value so that you can effortlessly read what truly matters and save lots of time.

To change the cell color based on the value in Excel:

  • One of your columns must be named Status (just for the sake of the example).
  • Click the Developer tab in the Ribbon.
  • Click Visual Basic or Alt+F11 to open your developer VBA editor.
  • Highlight the Worksheet where you would like to run your code or select a module that contains your VBA code.
  • Go ahead and paste the code below. In this example, we modify the inner color of a range of cells to specific cell RGB values corresponding to the red, yellow, and green colors.

Sub Color_Cell_Condition()

Dim MyCell As Range
Dim StatValue As String
Dim StatusRange As Range

Set StatusRange = Range("Status")

For Each MyCell In StatusRange
StatValue = MyCell.Value
Select Case StatValue

Case "Done"
MyCell.EntireRow.Interior.Color = RGB(0, 255, 0)

Case "In Progress"
MyCell.EntireRow.Interior.Color = RGB(255, 255, 0)

Case "Blocked"
MyCell.EntireRow.Interior.Color = RGB(255, 0, 0)

End Select
Next
MyCell

End Sub

  • Run your code by pressing on F5 or Run > Run Sub / UserForm.
  • Save the piece of code and close the coding editor.
any more questions about excel? check out our forum!

Subject Replies
How to calculate positions(1st,2nd,3rd...) in Excel 4
Unable to change date format in Excel 39
How to convert number into text in Excel 9
Convert number to spelling 28
Date formatting in dd/mm/yyyy 11

Excel