Macro needed to highlight a row

[Maggie]

I have a worksheet named Data that I would like for a row to be
highlighted when someone enters Paid Off, Missing and Other . How do
I do a macro to perform this? I would like each type to be a
different color such as Paid off to be highlighted yellow, Missing
red,and Other pink.
Please help

 

[Barb Reinhardt]

Have you considered using a conditional format to do this? I also presume
that Missing/Paid Off/Other are all entered in the same column? If so, you
could set a condition for values on that column to change the entire row.

Come back if you need more help.

 

[Maggie]

Have you considered using a conditional format to do this?    I also presume
that Missing/Paid Off/Other are all entered in the same column?  If so,you
could set a condition for values on that column to change the entire row. 

Come back if you need more help.
--
HTH,
Barb Reinhardt

If this post was helpful to you, please click YES below.





- Show quoted text -

I have tried conditional formatting but it will only highlight the
cell. The data entered is in three different columns.

 

[Maggie]

I have tried conditional formatting but it will only highlight the
cell.  The data entered is in three different columns.- Hide quoted text -

- Show quoted text -

And the data is coming from a drop down.

 

[RadarEye]

I have a worksheet named Data that I would like for a row to be
highlighted when someone enters Paid Off, Missing and Other .  How do
I do a macro to perform this?  I would like each type to be a
different color  such as Paid off to be highlighted yellow, Missing
red,and Other pink.
Please help

Hai Maggie,

For starters VBA has 8 shorthand colors
vbBlack
vbRed
vbGreen
vbYellow
vbBlue
vbMagenta
vbCyan
vbWhite

Next to this you can use a colorindex.

To come back to your question:
Copy the macro below into the macropar of the sheet where you want the
colorchanging occur

Private Sub Worksheet_Change(ByVal Target As Range)
Select Case Target.Value
Case "Paid off": Target.EntireRow.Interior.Color = vbYellow
Case "Missing": Target.EntireRow.Interior.Color = vbRed
Case "Other": Target.EntireRow.Interior.Color = vbMagenta
End Select
End Sub


HTH,
Wouter

 

[Maggie]

Hai Maggie,

For starters VBA has 8 shorthand colors
vbBlack
vbRed
vbGreen
vbYellow
vbBlue
vbMagenta
vbCyan
vbWhite

Next to this you can use a colorindex.

To come back to your question:
Copy the macro below into the macropar of the sheet where you want the
colorchanging occur

Private Sub Worksheet_Change(ByVal Target As Range)
    Select Case Target.Value
        Case "Paid off": Target.EntireRow.Interior.Color = vbYellow
        Case "Missing":  Target.EntireRow.Interior.Color = vbRed
        Case "Other":    Target.EntireRow.Interior.Color = vbMagenta
    End Select
End Sub

HTH,
Wouter

Thanks, that worked perfect, but if I accidently put in Paid Off I
have to manually change the fill is there a way when I delete the
entry, it will put the fill back to original?

 

[Maggie]

Thanks, that worked perfect, but if I accidently put in Paid Off I
have to manually change the fill is there a way when I delete the
entry, it will put the fill back to original?- Hide quoted text -

- Show quoted text -

Now it is saying that I need to debug here :
Target.EntireRow.Interior.Color = vbYellow
Help.

 

[Maggie]

Thanks, that worked perfect, but if I accidently put in Paid Off I
have to manually change the fill is there a way when I delete the
entry, it will put the fill back to original?- Hide quoted text -

- Show quoted text -

I figured that it causes a debug when I protect the sheet and I need
to protect the sheet because there are formulas.

 

[Rick Rothstein]

Try this...

Private Sub Worksheet_Change(ByVal Target As Range)
Select Case Target.Value
Case "Paid off"
Target.EntireRow.Interior.Color = vbYellow
Case "Missing"
Target.EntireRow.Interior.Color = vbRed
Case "Other"
Target.EntireRow.Interior.Color = vbMagenta
Case Else
Target.EntireRow.Interior.ColorIndex = xlColorIndexAutomatic
End Select
End Sub

Note that except for the Case Else and the code line under it, this is the
same code as RadarEye posted... I simply removed the "join multiple code
lines" symbol (the colon) and physically split the joined lines into
separate lines (which is my personal preference).

--
Rick (MVP - Excel)

Hai Maggie,

For starters VBA has 8 shorthand colors
vbBlack
vbRed
vbGreen
vbYellow
vbBlue
vbMagenta
vbCyan
vbWhite

Next to this you can use a colorindex.

To come back to your question:
Copy the macro below into the macropar of the sheet where you want the
colorchanging occur

Private Sub Worksheet_Change(ByVal Target As Range)
Select Case Target.Value
Case "Paid off": Target.EntireRow.Interior.Color = vbYellow
Case "Missing": Target.EntireRow.Interior.Color = vbRed
Case "Other": Target.EntireRow.Interior.Color = vbMagenta
End Select
End Sub

HTH,
Wouter

Thanks, that worked perfect, but if I accidently put in Paid Off I
have to manually change the fill is there a way when I delete the
entry, it will put the fill back to original?

 

[Maggie]

Try this...

Private Sub Worksheet_Change(ByVal Target As Range)
    Select Case Target.Value
        Case "Paid off"
            Target.EntireRow.Interior.Color = vbYellow
        Case "Missing"
            Target.EntireRow.Interior.Color = vbRed
        Case "Other"
            Target.EntireRow.Interior.Color = vbMagenta
        Case Else
            Target.EntireRow.Interior.ColorIndex = xlColorIndexAutomatic
    End Select
End Sub

Note that except for the Case Else and the code line under it, this is the
same code as RadarEye posted... I simply removed the "join multiple code
lines" symbol (the colon) and physically split the joined lines into
separate lines (which is my personal preference).

--
Rick (MVP - Excel)










Thanks, that worked perfect, but if I accidently put in Paid Off I
have to manually change the fill is there a way when I delete the
entry, it will put the fill back to original?- Hide quoted text -

- Show quoted text -

Why won't the macro work when I protect the sheet?

 

[Gord Dibben]

Why won't the macro work when I protect the sheet?

Protect in the event.

Private Sub Worksheet_Change(ByVal Target As Range)
Protect Password:="password", userinterfaceonly:=True

rest of code


Gord Dibben MS Excel MVP