Change cell color based on another cell value

[chris46521]

I would like to have the fill color of a cell column B change if the
value in another cell in the same row is "Y" or "y." I don't want to
use conditional formatting because there are already a large number of
Ys in column H and the info in column B needs to remain the same for
those cells. I want the cells to change color just for new entries. I
tried copying code from another worksheet but I could not get it to
work. What would be a simple code for doing this? Thanks!

 

[Excelenator]

Where do your new entries go? Why not JUST conditionally format the new
entries leaving the old ones alone?

 

[chris46521]

Thanks for your response. The data is scattered throughout the
spreadsheet and to me this seems to work more practically. I did try
conditional formating, but I couldn't get it to work either when I
entered the formula. There must be a simple code to perform this
action. Thanks for your help!

 

[Bob Phillips]

Conditional formatting seems tailor made for what you want. See
http://www.contextures.com/xlCondFormat01.html

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

 

[Excelenator]

Ok since you are determined to do this through code here you go. Plac
this in the Sheet section of the VBE where your data resides.


Code
-------------------
Private Sub Worksheet_Change(ByVal Target As Range)
'If you already have data in Column H and then enter
'data in column B this will conditionally make the
'data in column B bold/italic if H = Y or y
If Target.Column = 2 Then
If UCase(Range("H" & Target.Row).Value) = "Y" Then
With Target.Font
.Bold = True
.Italic = True
End With
End If
End If
'If you already have data in Column B and then enter
'data in column H this will conditionally make the
'data in column B bold/italic if H = Y or y and will
'remove the bold/italic if column H's value is NOT
'Y or y
If Target.Column = 8 Then
Dim tf As Boolean
tf = True

If UCase(Target.Value) <> "Y" Then
tf = False

End If
With Target.Offset(0, -6).Font
.Bold = tf
.Italic = tf
End With
End If
End Su
-------------------

 

[Excelenator]

I just noticed you wanted the fill color changed and not bold italic an
in changing this I found that I could simplify the code significantly.


Code
-------------------
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 8 Then
If UCase(Target.Value) <> "Y" Then
Target.Offset(0, -6).Interior.ColorIndex = xlNone
Else
Target.Offset(0, -6).Interior.ColorIndex = 36
End If
End If
End Su
-------------------

 

[chris46521]

Thanks so much guys for your help!