change cell color (office 2000)

Topic

New Reply

Good Evening To all in the know,

I know that we can use conditional formatting to get a cell to change color based on the value of the cell with in certain ranges. This however has its limitations to only 3 conditions, thus 3 different colors. My question is … Is there any other way to increase the numbers of color choices as the ranges get smaller. (ie) cell A-1 .. if the cell value is 1 the color is blue .. range 1-2 … if the cell value is 3 .. color of cell will be brown… range 3-4… if cell value is 5.. color is pink.. range 5-6 … etc. … out to say 50 or so. Can anyone point me in the direction of a macro that could do this, with values that extend to the 50 or even higher??? Can someone point me in the direction of finding the numeric code for each of the colors in the palette???

Second part of this question… I believe using an if then statement also has its limits … would visual basic ???

Thanks in advance for all the help and I hope I explained my question well enough !!
cvbs

Reply | Quote

Replies

You can use VBA code in the Worksheet_Change event to specify any valid color depending on the cell value or a more complex condition. You’ll find examples in post 557,373, post 683,138, post 534,277 etc.

The attached workbook lists all 56 colors with their index.

I don’t understand the second part of your question.

Reply | Quote

Good Evening Hans,

Trying to adapt your code to the question I have posed would this work?

Dim oCell As Range
If Intersect(Target, Range(“A1:C20”)) Is Nothing Then Exit Sub
For Each oCell In Intersect(Target, Range(“A1:C20”))
Select Value oCell.Value
Value 1, 2
oCell.Font.ColorIndex = 9
Value 3, 4
oCell.Font.ColorIndex = 5
Value 5, 6
oCell.Font.ColorIndex = 10
Value 7, 8
oCell.Font.ColorIndex = 7
Value 9, 10
oCell.Font.ColorIndex = 1
End Select
Next oCell
End Sub

Reply | Quote

As you can see in the posts I referred to, the syntax is Select Case … End Select, not Select Value … End Select:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim oCell As Range
If Intersect(Target, Range(“A1:C20”)) Is Nothing Then Exit Sub
For Each oCell In Intersect(Target, Range(“A1:C20”))
Select Case oCell.Value
Case 1, 2
oCell.Font.ColorIndex = 9
Case 3, 4
oCell.Font.ColorIndex = 5
Case 5, 6
oCell.Font.ColorIndex = 10
Case 7, 8
oCell.Font.ColorIndex = 7
Case 9, 10
oCell.Font.ColorIndex = 1
End Select
Next oCell
End Sub

Reply | Quote

Good Evening Hans,

I will be the first to admit it …. I am lost. I have copied your macro into the attached sheet and tried to run it and I get an error. Can you please assist me to getting this corrected?

Thank you again for the EXCELLENT support !

Reply | Quote

The code in your worksheet works correctly if I try it. What does the error message say and on which line does it occur?

Reply | Quote

Hans,

Thank you for the quick response. I get a “Run-time eerror # 424 Object required. When I press the debug it highlights the first If statement line.

Thank you for your help.
cvbs

Reply | Quote

That error will occur if you try to run the macro named something in Module2. This macro is not valid. I’d delete this macro (or remove the module).
But the code in the Sheet1 module should run automatically and without errors if you enter or change a cell value on Sheet1, for example if you enter a D or N in cell A1.
If you get an error when you change a cell value, I have no idea why that happens – the code doesn’t depend on the version of Excel as far as I can tell.

Reply | Quote

Further to Hans’ code; if you want to control the colour of a cell that changes as a consequence of a precedent changing, the “For” statement must change to:

For Each oCell In Range(“A1:C20”)

Be aware that any cell being controlled by the macro must lie within the identified range; and a large range will slow down the process significantly.

Reply | Quote

First of all Hans,

Thanks, I deleted the module and the sheet macro did work w/ out incident.
Just to set my head straight on this … are all macros that are tied to “sheets” AUTO… and those tied to modules can only be run by invoking the name of the macro?? and if so …. If I have a macro that is in a “sheet” can I transfer it to another workbook by coping it to a module w/ in said workbook and giving it a name accordingly ??

Don,

Thanks for the heads up much appreciated.

Thanks again for allowing me to ask the “dumb questions” !
cvbs

Reply | Quote

All macros are tied to some event. The ones in the worksheet and workbook objects are “auto” in the sense that they are tied to routine events: changing the worksheet, selecting new cells, dbl-clicking, activating/deactivating the sheet, etc. They can even be tied to changing an object on the sheet.

The ones in the general module are less “auto”. They can be linked to buttons or other objects (via “Assign macro”) or called via Tools – macro, or added to toolbar items. This is also the place for user defined functions which in some sense are also “auto”

The ones created in worksheets/books are defined in their name what event triggers them. The ones in the modules have to be assigned to something or called explicilty.

Steve

Reply | Quote

Thanks Steve,

I think I am understanding, still trying to get my head wrapped around this whole thing, so please bare with me for a few momnets

…. so if I wanted to transfer this to another workbook alltogether I should attache it to the specific sheet?? What would be needed to be changed in the coding to make this portable to a “module” and call it specifi by “button”?? or is that possible at all??

I have another question to the “Dim oCell as … ” what does putting the “o” in front actually tell VBA?

Thanks again,
cvbs

Reply | Quote

The “o” in front of oCell does not tell VBA anything. It is simply a naming convention that tell people reading the macro that the variable is an Object variable and therefore requires the use of the SET statement to assign a value to it. It is the DIM oCell statement that tells VBA something. There is absolutely nothing that would prevent you from using the DIM statement DIM oCell As Integer and then using oCell to hold integer values. However, that might confuse people reading the macro code.

What might have to be changed in a Sub Procedure to be able to copy it and paste it into another workbook depends completely on what is in the code in the procedure. For example, if the code is in a Worksheet Module and uses the ME. object to refer to the worksheet, and you copy and paste the code into a regular module in another workbook, then ME. will be undefined when the macro runs and must be changed to another way of refering to the worksheet like ActiveSheet. One thing that most likely would always need to be changed when moving code from an of the special modules like the a WorkSheet module to a regular module is the procedure name. Having a procedure with one of the special event procedure names like Worksheet_Change in a regular module could confuse VBA.

Reply | Quote

Large,

Thank you ….. I appreciate the insight…
I am ssssslllllllooooooowwwwwwly getting this into my head.

cvbs

Reply | Quote

My bad,

Thank you LEGUARE !,

I hit the send button to soon !!! .. please accept my apology

Reply | Quote

I have got to stop this drinking….

The name is Legare

Reply | Quote

Good Morning All,

I am trying to use the following to have each cell in a column of numbers in Row “M” be evaluated against a constant cell “m6” to see if that cell is greater or lesser than “m6” and then color the interior depending on the % over or under. At the “select Case” line is where the error starts.

Any help is greatly appreciated.
Thanks in advance,
cvbs

Sub custcolor()
Dim mycust As Range
Dim colchoice As Integer
Dim lrow As Integer
Dim lcell As String
Dim LColorCells As String
lrow = 11
LColorCells = “m” & lrow
Set mycust = Range(“m11”, “m112”)
For Each cell In mycust
Select Case (mycust * m6)
Case Is >= 1.1
colchoice = 6

Reply | Quote

You’re looking at each cell in mycust, so you should compare that cell to M6, not mycust. And you can’t use m6 to refer to cell M6, you must use Range(“M6”) or [M6].
Try

Select Case cell * Range(“m6”)

Reply | Quote

Good Morning Hans,

Thank You…. that worked for that cell comparison. Would it be best to Name cell M6 to make it easier to compare it to each cell in the column starting w/ cell M11 and going down through. I ask because I want to change each cell interior color, based on ranges, depending on it % relation to cell M6?

Thank you again for all the help,
cvbs

Reply | Quote

It doesn’t matter whether you give cell M6 a name, but it might make the code more readable.

Reply | Quote

Hans,

Select Case cell * Range(“m6”) — Only compares the first cell in the column .. what do you think would be the best way to have this work through each cell in the colum?

later,
cvbs

Reply | Quote

The code

Set mycust = Range(“m11”, “m112”)
For Each cell In mycust
…
Next cell

should loop through all cells in the range M11:M112, so I’m not sure what you mean.
Or do you mean that M6 should be varied?

Reply | Quote

Hans,

First of all .. that is true …. the For, Next does the trick … I was wondering if there is a better solution.

Secondly If you look at the following you will see that I have two columns written, however I have 3 others to do. As it stands this vba will work … My questions is there anyway to restructure the SelectCase cell / Range(“m6”) statement to include the ….(“p6”) and so on for the rest of the columns(s,v andy) – so I do not have to have the several case statements written over and over again.
Thanks for putting up with me … It is very much appreciated !!
cvbs

Sub custcolor()
Dim mycust As Range
Dim mymult As Range
Dim mypthn As Range
Dim myar As Range
Dim myrxsun As Range
Dim colchoice As Integer
Dim lrow As Integer
Dim lcell As String
Dim LColorCells As String
lrow = 11
LColorCells = “m” & lrow
Set mycust = Range(“m11”, “m112”)
Set mymult = Range(“p11”, “p112”)
Set mypthin = Range(“s11”, “s112”)
Set myar = Range(“v11”, “v112”)
Set myrxsun = Range(“y11”, “y112”)
For Each cell In mycust
Select Case cell / Range(“m6”)
Case Is >= 1.1
colchoice = 6
Case Is >= 0.95
colchoice = 35
Case Is >= 0.8
colchoice = 8
Case Is >= 0.7
colchoice = 4
Case Is = 1.1
colchoice = 6
Case Is >= 0.95
colchoice = 35
Case Is >= 0.8
colchoice = 8
Case Is >= 0.7
colchoice = 4
Case Is < 0.7
colchoice = 38
End Select
cell.Interior.ColorIndex = colchoice
Next
End Sub

Sub Resetcust()
Range("m11:m112", "p11:p112").Select
Selection.Interior.ColorIndex = none
Range("l11").Select
End Sub

Reply | Quote

Here is an approach using a double loop:

Sub CustColor()
Dim intRow As Integer
Dim intCol As Integer
Dim ColChoice As Integer
For intCol = 13 To 25 Step 3 ‘ columns M, P, …, Y
For intRow = 12 To 112 ‘ rows 12 to 112
Select Case Cells(intRow, intCol) / Cells(6, intCol)
Case Is >= 1.1
ColChoice = 6
Case Is >= 0.95
ColChoice = 35
Case Is >= 0.8
ColChoice = 8
Case Is >= 0.7
ColChoice = 4
Case Is < 0.7
ColChoice = 38
End Select
Cells(intRow, intCol).Interior.ColorIndex = ColChoice
Next intRow
Next intCol
End Sub

Reply | Quote

Hans, Hans, Hans,

YOU ARE THE MAN !! That is SO nice, it will work extremely well.

Your bio reads ….so I decided to try my hand at posting a few replies myself. It got out of hand in a big way…

I for one am VERY GLAD it did and that YOU and all the cast behind the scenes here at Woody’s Lounge deserve a VERY BIG ROUND OF APPLAUSE !!!

I have ssssssssooooooooo much more to learn and I truly appreciate EVERYONE here taking the time and having the patience w/ all the dumb questions and giving of yourselves to teach and explain.!!!

A million times THANK YOU !!….
Life is good at Woody’s ! with people like you around !

Reply | Quote

Thank *you*

Reply | Quote