|
Color Cells by macro NOT conditional format
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! |
Color Cells by macro NOT conditional format
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! |
Color Cells by macro NOT conditional format
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! |
Color Cells by macro NOT conditional format
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! |
Color Cells by macro NOT conditional format
On Jul 25, 2:22 pm, 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! 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. |
Color Cells by macro NOT conditional format
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! |
Color Cells by macro NOT conditional format
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 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. |
Color Cells by macro NOT conditional format
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! |
Color Cells by macro NOT conditional format
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" 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. |
Color Cells by macro NOT conditional format
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" 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. |
| All times are GMT +1. The time now is 12:19 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com