asked on

Shade cell color every time something is entered in an adjacent cell.

Hi, When I enter something new or change something in Cell B1, I would like Cell C1 to be shaded yellow. I tried this with Conditional Formatting, but I believe that you need to have a specific value in order for conditional formatting to trigger the change. I want this shading in C1 to be triggered if anything is added or changed in B1. In other words, B1 could be blank or already populated, so if there is any change entered to that cell, I need C1 to alert me by shading it yellow. I then need to copy it to subsequent rows.

I hope my request makes sense and is clear.

Thank you!

HainKurt

here

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Column = 2 Then
    'MsgBox "There was a change in cell B" & Target.Row
    With Cells(Target.Row, 3).Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
  End If
End Sub

Open in new window

* should be in a module that belongs to a worksheet
29050706.xlsm

angelfromabove

ASKER

Perfect, Thank you for the prompt succinct response!

Rob Henson

HainKurt's code will do what you want but will apply a permanent formatting to the cell.

Extending your question, what happens if the entry is then changed again or changed back to what it was before? How should that affect the formatting?

There is a function in Excel for Tracking Changes but I have not had much success with it on the couple of occasions that I have used it.

Track Changes will make a list of changes in a separate sheet; listing cell reference, previous value and new value.

Thanks
Rob H

angelfromabove

ASKER

Thanks Rob!

I see your point- hmmm, I don't necessarily need to track changes, but it would help if the code could be expanded to remove the permanent formatting if it's changed back to what it was originally. If it's changed again, that's fine if the shading remains, only if it goes back to the original entry would I need it to revert to being non-formatted.

@HainKurt, can you opine on that?

ASKER CERTIFIED SOLUTION

HainKurt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

angelfromabove

ASKER

Perfect, thank you.