How to change the background color of entire row in Excel using VBA code - Conditional

My friend Vinod gave me a problem to change the background color of entire row to Red and also to change the font to bold. It seems to be an easy task but there are two constraints.
1. He want to do this only for those lines which has font color of red.
2. There are 2500 entries in the excel.

Now if you have plenty of time then you can do this task in some hundred years :) but I am a Lazy guy so I wrote this little Macro code.

Private Sub Workbook_Open()
For Each cell In Worksheets("sheet1").Range(Worksheets("sheet1").Range("A1"), _
Worksheets("sheet1").Range("A1").End(xlDown))
        If cell.Font.Color = RGB(255, 0, 0) Then
            cell.Font.Color = RGB(0, 0, 0)
          cell.EntireRow.Font.Bold = True
          cell.EntireRow.Interior.Color = RGB(255, 0, 0)
        End If
       
    Next cell
End Sub

You can write this code either in the Open Event of the Workbook or write a Module for it.

For Each cell In Worksheets("sheet1").Range(Worksheets("sheet1").Range("A1"), Worksheets("sheet1").Range("A1").End(xlDown))

This code simply defines the range starting from cell A1 till the bottom most cell which has any data.

End(xlDown))
This is similar to pressing Ctrl + Down Arrow

cell.Font.Color = RGB(0, 0, 0)
This code changes the font color of the cell content

cell.EntireRow.Font.Bold = True
This code changes the font to Bold

cell.EntireRow.Interior.Color = RGB(255, 0, 0)
This code fills the cell with Red color

Comments

been playing with this for a while finaly got it worked out for what i needed it to do. try this and let me know. Also it is set up for upercase inputs on the text. and if you want to change the color look up a RGB table and use the numbers from that.

Option Explicit
Const MYRANGE1 As String = "A1:G1"
Const MYRANGE2 As String = "A1:E1"
Sub Worksheet_Change(ByVal Target As Range)
Select Case UCase(Target.Value)
Case "DOWN"
Target.Range(MYRANGE1).Interior.Color = RGB(255, 0, 0)
Case "UP"
Target.Range(MYRANGE1).Interior.Color = RGB(0, 255, 0)
Case "NEEDS"
Target.Range(MYRANGE1).Interior.Color = RGB(255, 153, 0)
Case "OUT"
Target.Range(MYRANGE2).Interior.Color = RGB(72, 118, 255)
End Select
Application.EnableEvents = True

End Sub

Member since:
24 November 2007
Last activity:
4 weeks 4 days

@Jabir When you select Yes or No then you want to change the color of the specific cell?

In that case you can write a code similar to this.

Private Sub ComboBox1_Change()
Select Case ComboBox1.Text
Case "Yes"
 cell.EntireRow.Interior.Color = RGB(255, 0, 0)
Case "No"
 cell.EntireRow.Interior.Color = RGB(255, 255, 0)
Case "NA"
 cell.EntireRow.Interior.Color = RGB(255, 0, 255)
Case Else
End Select
End Sub

Member since:
24 November 2007
Last activity:
4 weeks 4 days

@saurav

>>>For Each cell In Worksheets("sheet1").Range("a:a")

This line will make the loop run for each cell in Column A. Whereas the code that I used here will run for all the cells from A2 till the last cell that has any data in it.

Private Sub xxx()
For Each cell In Worksheets("sheet1").Range("a:a")
If cell.Font.Color = RGB(255, 0, 0) Then
cell.Font.Color = RGB(0, 0, 0)
cell.EntireRow.Font.Bold = True
cell.EntireRow.Interior.Color = RGB(255, 0, 0)
End If

Next cell
End Sub

What I didn't understand how does the code "For Each cell In Worksheets("sheet1").Range(Worksheets("sheet1").Range("A1"), Worksheets("sheet1").Range("A1").End(xlDown))", make any difference to the output.

I just used "For Each cell In Worksheets("sheet1").Range("a:a")", and got the same output.

Ravi it will be great if you can explain me the code "For Each cell In Worksheets("sheet1").Range(Worksheets("sheet1").Range("A1"), Worksheets("sheet1").Range("A1").End(xlDown))", step by step.

Regards,
Saurav

Hi Siraj,
Hope you are doing good,

How to change a cell color using script or any other method based on condition.

i.e., I have a combo box in a cell having options "Yes", "No", & "NA", based on my selection I need different colors,
Eg: If I select Yes, then it should appear in Green, and if No in Red

Can you please help

Thanks & Regards
Jabir Muhiyudheen KK
Delhi