Hello,

I would like to know if there is a way to color a cell based on it's value,
but not using the conditional format command.

If the date in column B is more than 45 days old, make the background of
font bright pink.

I need lots of specific help because I'm not comfortable with the VB editor
yet.

Thank you!

Hi,

Im not at all sure about the pink but everyone to their own, Right click the
sheet tab, view code and paste this in

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
If Not Intersect(Target, Range("B:B")) Is Nothing Then
today = Now
If Target.Value < today - 45 Then
On Error Resume Next
Application.EnableEvents = False
Target.Font.ColorIndex = 7 ' for font
Target.Interior.ColorIndex = 7
Application.EnableEvents = True
On Error GoTo 0
End If
End If
End Sub

"DTTODGG" wrote:

> Hello,
>
> I would like to know if there is a way to color a cell based on it's value,
> but not using the conditional format command.
>
> If the date in column B is more than 45 days old, make the background of
> font bright pink.
>
> I need lots of specific help because I'm not comfortable with the VB editor
> yet.
>
> Thank you!

Thank you Mike for your oh-so-quick-reply!

I typed everything in as stated. Does something make it "go"? Do I need to
"run" this or "save" that or something?

Sorry for my lack of knowledge, but I'm learning everyday thanks to people
like you.

"Mike H" wrote:

> Hi,
>
> Im not at all sure about the pink but everyone to their own, Right click the
> sheet tab, view code and paste this in
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
> If Not Intersect(Target, Range("B:B")) Is Nothing Then
> today = Now
> If Target.Value < today - 45 Then
> On Error Resume Next
> Application.EnableEvents = False
> Target.Font.ColorIndex = 7 ' for font
> Target.Interior.ColorIndex = 7
> Application.EnableEvents = True
> On Error GoTo 0
> End If
> End If
> End Sub
>
> "DTTODGG" wrote:
>
> > Hello,
> >
> > I would like to know if there is a way to color a cell based on it's value,
> > but not using the conditional format command.
> >
> > If the date in column B is more than 45 days old, make the background of
> > font bright pink.
> >
> > I need lots of specific help because I'm not comfortable with the VB editor
> > yet.
> >
> > Thank you!

If you right click the sheet tab that you want to use the macro in, then
click view code, you will open the sheet code window. Copy Mike's code to
that window and save the file. Then when you change the Target cell, it
should apply the color.
"DTTODGG" wrote:

> Thank you Mike for your oh-so-quick-reply!
>
> I typed everything in as stated. Does something make it "go"? Do I need to
> "run" this or "save" that or something?
>
> Sorry for my lack of knowledge, but I'm learning everyday thanks to people
> like you.
>
> "Mike H" wrote:
>
> > Hi,
> >
> > Im not at all sure about the pink but everyone to their own, Right click the
> > sheet tab, view code and paste this in
> >
> > Private Sub Worksheet_Change(ByVal Target As Range)
> > If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
> > If Not Intersect(Target, Range("B:B")) Is Nothing Then
> > today = Now
> > If Target.Value < today - 45 Then
> > On Error Resume Next
> > Application.EnableEvents = False
> > Target.Font.ColorIndex = 7 ' for font
> > Target.Interior.ColorIndex = 7
> > Application.EnableEvents = True
> > On Error GoTo 0
> > End If
> > End If
> > End Sub
> >
> > "DTTODGG" wrote:
> >
> > > Hello,
> > >
> > > I would like to know if there is a way to color a cell based on it's value,
> > > but not using the conditional format command.
> > >
> > > If the date in column B is more than 45 days old, make the background of
> > > font bright pink.
> > >
> > > I need lots of specific help because I'm not comfortable with the VB editor
> > > yet.
> > >
> > > Thank you!

On Jul 25, 2:22 pm, JLGWhiz <JLGW...@discussions.microsoft.com> wrote:
> If you right click the sheet tab that you want to use the macro in, then
> click view code, you will open the sheet code window. Copy Mike's code to
> that window and save the file. Then when you change the Target cell, it
> should apply the color.
>
> "DTTODGG" wrote:
> > Thank you Mike for your oh-so-quick-reply!
>
> > I typed everything in as stated. Does something make it "go"? Do I need to
> > "run" this or "save" that or something?
>
> > Sorry for my lack of knowledge, but I'm learning everyday thanks to people
> > like you.
>
> > "Mike H" wrote:
>
> > > Hi,
>
> > > Im not at all sure about the pink but everyone to their own, Right click the
> > > sheet tab, view code and paste this in
>
> > > Private Sub Worksheet_Change(ByVal Target As Range)
> > > If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
> > > If Not Intersect(Target, Range("B:B")) Is Nothing Then
> > > today = Now
> > > If Target.Value < today - 45 Then
> > > On Error Resume Next
> > > Application.EnableEvents = False
> > > Target.Font.ColorIndex = 7 ' for font
> > > Target.Interior.ColorIndex = 7
> > > Application.EnableEvents = True
> > > On Error GoTo 0
> > > End If
> > > End If
> > > End Sub
>
> > > "DTTODGG" wrote:
>
> > > > Hello,
>
> > > > I would like to know if there is a way to color a cell based on it's value,
> > > > but not using the conditional format command.
>
> > > > If the date in column B is more than 45 days old, make the background of
> > > > font bright pink.
>
> > > > I need lots of specific help because I'm not comfortable with the VB editor
> > > > yet.
>
> > > > Thank you!

In my experience, running with the Worksheet_Change sub causes a
relative performance hit to how much code you add there. Be wary when
using it as it can cause frustration to users if the lag is to long
each time they click... even a half second delay is very noticable.

Thank you. Silly me, I thought it would just "update" all the current fields
I have in the column. It only changes when I modify the date. Is there a way
to "refresh" or "update" the current cells? Like "run macro" or something?

Anyway, this solution works wonderfully. I just need to know how to learn
this stuff myself - someday.

Thanks again!

"JLGWhiz" wrote:

> If you right click the sheet tab that you want to use the macro in, then
> click view code, you will open the sheet code window. Copy Mike's code to
> that window and save the file. Then when you change the Target cell, it
> should apply the color.
> "DTTODGG" wrote:
>
> > Thank you Mike for your oh-so-quick-reply!
> >
> > I typed everything in as stated. Does something make it "go"? Do I need to
> > "run" this or "save" that or something?
> >
> > Sorry for my lack of knowledge, but I'm learning everyday thanks to people
> > like you.
> >
> > "Mike H" wrote:
> >
> > > Hi,
> > >
> > > Im not at all sure about the pink but everyone to their own, Right click the
> > > sheet tab, view code and paste this in
> > >
> > > Private Sub Worksheet_Change(ByVal Target As Range)
> > > If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
> > > If Not Intersect(Target, Range("B:B")) Is Nothing Then
> > > today = Now
> > > If Target.Value < today - 45 Then
> > > On Error Resume Next
> > > Application.EnableEvents = False
> > > Target.Font.ColorIndex = 7 ' for font
> > > Target.Interior.ColorIndex = 7
> > > Application.EnableEvents = True
> > > On Error GoTo 0
> > > End If
> > > End If
> > > End Sub
> > >
> > > "DTTODGG" wrote:
> > >
> > > > Hello,
> > > >
> > > > I would like to know if there is a way to color a cell based on it's value,
> > > > but not using the conditional format command.
> > > >
> > > > If the date in column B is more than 45 days old, make the background of
> > > > font bright pink.
> > > >
> > > > I need lots of specific help because I'm not comfortable with the VB editor
> > > > yet.
> > > >
> > > > Thank you!

What are some other options?
For this particular case, I think everything will work out fine, but I'd
like to learn about other options for the future.

"theSquirrel" wrote:

> On Jul 25, 2:22 pm, JLGWhiz <JLGW...@discussions.microsoft.com> wrote:
> > If you right click the sheet tab that you want to use the macro in, then
> > click view code, you will open the sheet code window. Copy Mike's code to
> > that window and save the file. Then when you change the Target cell, it
> > should apply the color.
> >
> > "DTTODGG" wrote:
> > > Thank you Mike for your oh-so-quick-reply!
> >
> > > I typed everything in as stated. Does something make it "go"? Do I need to
> > > "run" this or "save" that or something?
> >
> > > Sorry for my lack of knowledge, but I'm learning everyday thanks to people
> > > like you.
> >
> > > "Mike H" wrote:
> >
> > > > Hi,
> >
> > > > Im not at all sure about the pink but everyone to their own, Right click the
> > > > sheet tab, view code and paste this in
> >
> > > > Private Sub Worksheet_Change(ByVal Target As Range)
> > > > If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
> > > > If Not Intersect(Target, Range("B:B")) Is Nothing Then
> > > > today = Now
> > > > If Target.Value < today - 45 Then
> > > > On Error Resume Next
> > > > Application.EnableEvents = False
> > > > Target.Font.ColorIndex = 7 ' for font
> > > > Target.Interior.ColorIndex = 7
> > > > Application.EnableEvents = True
> > > > On Error GoTo 0
> > > > End If
> > > > End If
> > > > End Sub
> >
> > > > "DTTODGG" wrote:
> >
> > > > > Hello,
> >
> > > > > I would like to know if there is a way to color a cell based on it's value,
> > > > > but not using the conditional format command.
> >
> > > > > If the date in column B is more than 45 days old, make the background of
> > > > > font bright pink.
> >
> > > > > I need lots of specific help because I'm not comfortable with the VB editor
> > > > > yet.
> >
> > > > > Thank you!
>
> In my experience, running with the Worksheet_Change sub causes a
> relative performance hit to how much code you add there. Be wary when
> using it as it can cause frustration to users if the lag is to long
> each time they click... even a half second delay is very noticable.
>
>

Mike,

As far as the Pink goes, I just wanted something different.
Where do I find the values of other colors and fonts?

Thanks again.

"Mike H" wrote:

> Hi,
>
> Im not at all sure about the pink but everyone to their own, Right click the
> sheet tab, view code and paste this in
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
> If Not Intersect(Target, Range("B:B")) Is Nothing Then
> today = Now
> If Target.Value < today - 45 Then
> On Error Resume Next
> Application.EnableEvents = False
> Target.Font.ColorIndex = 7 ' for font
> Target.Interior.ColorIndex = 7
> Application.EnableEvents = True
> On Error GoTo 0
> End If
> End If
> End Sub
>
> "DTTODGG" wrote:
>
> > Hello,
> >
> > I would like to know if there is a way to color a cell based on it's value,
> > but not using the conditional format command.
> >
> > If the date in column B is more than 45 days old, make the background of
> > font bright pink.
> >
> > I need lots of specific help because I'm not comfortable with the VB editor
> > yet.
> >
> > Thank you!

ColorIndex numbers
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)
'--
1 Black
2 White
3 Red
4 Bright Green
5 Blue
6 Yellow
7 Pink
8 Turquoise
9 Dark Red
10 Green
11 Dark Blue
12 Dark Yellow
13 Violet
14 Teal
15 Gray-25%
16 Gray-50%
17 Periwinkle
18 Plum
19 Ivory
20 Light Turquoise
21 Dark Purple
22 Coral
23 Ocean Blue
24 Ice Blue
25 Dark Blue
26 Pink
27 Yellow
28 Turquoise
29 Violet
30 Dark Red
31 Teal
32 Blue
33 Sky Blue
34 Light Turquoise
35 Light Green
36 Light Yellow
37 Pale Blue
38 Rose
39 Lavender
40 Tan
41 Light Blue
42 Aqua
43 Lime
44 Gold
45 Light Orange
46 Orange
47 Blue-Gray
48 Gray-40%
49 Dark Teal
50 Sea Green
51 Dark Green
52 Olive Green
53 Brown
54 Plum
55 Indigo
56 Gray-80%
'-----------------



"DTTODGG"
<(E-Mail Removed)>
wrote in message
Mike,
As far as the Pink goes, I just wanted something different.
Where do I find the values of other colors and fonts?
Thanks again.

Thank you Jim!

"Jim Cone" wrote:

> ColorIndex numbers
> --
> Jim Cone
> San Francisco, USA
> http://www.realezsites.com/bus/primitivesoftware
> (Excel Add-ins / Excel Programming)
> '--
> 1 Black
> 2 White
> 3 Red
> 4 Bright Green
> 5 Blue
> 6 Yellow
> 7 Pink
> 8 Turquoise
> 9 Dark Red
> 10 Green
> 11 Dark Blue
> 12 Dark Yellow
> 13 Violet
> 14 Teal
> 15 Gray-25%
> 16 Gray-50%
> 17 Periwinkle
> 18 Plum
> 19 Ivory
> 20 Light Turquoise
> 21 Dark Purple
> 22 Coral
> 23 Ocean Blue
> 24 Ice Blue
> 25 Dark Blue
> 26 Pink
> 27 Yellow
> 28 Turquoise
> 29 Violet
> 30 Dark Red
> 31 Teal
> 32 Blue
> 33 Sky Blue
> 34 Light Turquoise
> 35 Light Green
> 36 Light Yellow
> 37 Pale Blue
> 38 Rose
> 39 Lavender
> 40 Tan
> 41 Light Blue
> 42 Aqua
> 43 Lime
> 44 Gold
> 45 Light Orange
> 46 Orange
> 47 Blue-Gray
> 48 Gray-40%
> 49 Dark Teal
> 50 Sea Green
> 51 Dark Green
> 52 Olive Green
> 53 Brown
> 54 Plum
> 55 Indigo
> 56 Gray-80%
> '-----------------
>
>
>
> "DTTODGG"
> <(E-Mail Removed)>
> wrote in message
> Mike,
> As far as the Pink goes, I just wanted something different.
> Where do I find the values of other colors and fonts?
> Thanks again.
>
>