Monday, July 4, 2011

Change the color of row on the basis of value entered in cell using VBA

if you want to change the color of a row on the basis of value entered in a cell.

Here is the code which you can use in workshseet change event-

For example - if some one type A in any cell of column "A" then entire row dhould be filled with red color

Private Sub Worksheet_Change(ByVal Target As Range)

Dim cell As Range

Dim abc As Range
' taken target.column = 1 bcoz "a" is 1st column
If Target.Column = 1 Then
Set abc = Range("A" & Target.Row & ":A" & Target.Row + Target.Rows.Count)

For Each cell In abc
Select Case cell.Value
Case "A"
cell.EntireRow.Interior.Color = RGB(255, 0, 0)
Case "B"
cell.EntireRow.Interior.Color = RGB(0, 0, 255)
Case "C"
cell.EntireRow.Interior.Color = RGB(255, 255, 0)
Case Else
cell.EntireRow.Interior.Color = xlNone
End Select
Next cell
End If
End Sub

1 comment:

  1. Download Excel File