Cell shading macro help

NuttyBuddy

New Member
Joined
Nov 2, 2002
Messages
6
Hello! I need a macro which will color a range of cells a color if a certain criteria is met. I can't use conditional formatting because I have more than four cell shading tests to perform. Here is my setup:

In my worksheet, the cells cover a range of grids C5 through Y254 - thats a 23x250 rectangular cell grid. Each cell contains an 8 character text value.

1)If the left most character of the cell contents (text) is "Y", I want the cell background green - which is color 43 in my custom color grid.

2)If the 2 left most characters of the cell contents (text) is "N1", I want the cell background blue - which is color 55 in my custom color grid.

How would I attain 1 and 2 above in a macro?

A macro for the above two examples I supplied should allow me to expand the tests to the other criteria I need.

Thanks for the help!

Buddy
 

Some videos you may like

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

Barrie Davidson

MrExcel MVP
Joined
Feb 10, 2002
Messages
2,330
Right click on the worksheet and paste this code in to the worksheet's code:

<PRE>
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim Intersect As Range
Application.EnableEvents = False
Set Intersect = Application.Intersect(Range("C5:Y254"), Target)
If Intersect Is Nothing Then
Application.EnableEvents = True
Exit Sub
End If
If Left(Target.Value, 1) = "Y" Then
Target.Interior.ColorIndex = 43
ElseIf Left(Target.Value, 2) = "N1" Then
Target.Interior.ColorIndex = 55
End If
Application.EnableEvents = True
End Sub

</PRE>

NOTE - this is case sensitive (i.e., it won't change the colour if the word entered is yellow but will change it if you enter Yellow).

Regards,
 

NuttyBuddy

New Member
Joined
Nov 2, 2002
Messages
6
Thanks Barrie.

I copied and pasted into the worksheet code as you instructed. How do I get the code to execute?

I now have a Visual Basic window with "Worksheet" and "Change" above the edit window which contains your provided code. The colors in the worksheet have yet to change. Here is the worksheet so far saved as a web page:
http://home.mindspring.com/~nbuddy/Test.htm

Here is how it will ultimately look (sort of):
http://home.mindspring.com/~bconyers/alert1.htm

I have created a crude macro to do the above, but it is very long because I only know how to color one column at a time and one color at a time - I have 255 lines of macro to do just one color! It took me two macros to accomplish the above due to macro size constraints.

Buddy
 

Barrie Davidson

MrExcel MVP
Joined
Feb 10, 2002
Messages
2,330
Oh sorry, I thought you wanted the colour to change when you made a change to the worksheet. I didn't realize you wanted to change the colour based on existing data. Try this:

<PRE>
Sub ChangeColour()
'Written by Barrie Davidson
Application.ScreenUpdating = False
For Each c In Range("C5:Y254")
If Left(c.Value, 1) = "Y" Then
c.Interior.ColorIndex = 43
ElseIf Left(c.Value, 2) = "N1" Then
c.Interior.ColorIndex = 55
End If
Next c
Application.ScreenUpdating = True
End Sub

</PRE>

Does this help you out?
 

Watch MrExcel Video

Forum statistics

Threads
1,127,705
Messages
5,626,392
Members
416,180
Latest member
Excel_user12321

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top