Change cell color based on another cell value
 

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!


--
chris46521
------------------------------------------------------------------------
chris46521's Profile: http://www.excelforum.com/member.php...o&userid=35909
View this thread: http://www.excelforum.com/showthread...hreadid=568080

Change cell color based on another cell value
 

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


--
Excelenator


------------------------------------------------------------------------
Excelenator's Profile: http://www.excelforum.com/member.php...o&userid=36768
View this thread: http://www.excelforum.com/showthread...hreadid=568080

Change cell color based on another cell value
 

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!

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


--
chris46521
------------------------------------------------------------------------
chris46521's Profile: http://www.excelforum.com/member.php...o&userid=35909
View this thread: http://www.excelforum.com/showthread...hreadid=568080

Change cell color based on another cell value
 

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)

"chris46521" wrote
in message ...

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!


--
chris46521
------------------------------------------------------------------------
chris46521's Profile:
http://www.excelforum.com/member.php...o&userid=35909
View this thread: http://www.excelforum.com/showthread...hreadid=568080

Change cell color based on another cell value
 

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
-------------------


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

--
Excelenato

-----------------------------------------------------------------------
Excelenator's Profile: http://www.excelforum.com/member.php...fo&userid=3676
View this thread: http://www.excelforum.com/showthread.php?threadid=56808

Change cell color based on another cell value
 

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
-------------------

--
Excelenato

-----------------------------------------------------------------------
Excelenator's Profile: http://www.excelforum.com/member.php...fo&userid=3676
View this thread: http://www.excelforum.com/showthread.php?threadid=56808

Change cell color based on another cell value
 

Thanks so much guys for your help!


--
chris46521
------------------------------------------------------------------------
chris46521's Profile: http://www.excelforum.com/member.php...o&userid=35909
View this thread: http://www.excelforum.com/showthread...hreadid=568080