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

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

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

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

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

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

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