Page 1 of 2 12 LastLast
Results 1 to 15 of 26
  1. #1
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Harrisburg, Pennsylvania, USA
    Posts
    168
    Thanks
    1
    Thanked 0 Times in 0 Posts

    change cell color (office 2000)

    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: change cell color (office 2000)

    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 557,373</post:>, <post:=683,138>post 683,138</post:>, <post:=534,277>post 534,277</post:> etc.

    The attached workbook lists all 56 colors with their index.

    I don't understand the second part of your question.
    Attached Files Attached Files

  3. #3
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Harrisburg, Pennsylvania, USA
    Posts
    168
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: change cell color (office 2000)

    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

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: change cell color (office 2000)

    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

  5. #5
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Harrisburg, Pennsylvania, USA
    Posts
    168
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: change cell color (office 2000)

    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 !
    Attached Files Attached Files

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: change cell color (office 2000)

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

  7. #7
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Harrisburg, Pennsylvania, USA
    Posts
    168
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: change cell color (office 2000)

    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

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: change cell color (office 2000)

    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.

  9. #9
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: change cell color (office 2000)

    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.
    Regards
    Don

  10. #10
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Harrisburg, Pennsylvania, USA
    Posts
    168
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: change cell color (office 2000)

    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" ! <img src=/S/blush.gif border=0 alt=blush width=15 height=15>
    cvbs

  11. #11
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: change cell color (office 2000)

    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

  12. #12
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Harrisburg, Pennsylvania, USA
    Posts
    168
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: change cell color (office 2000)

    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 <img src=/S/bash.gif border=0 alt=bash width=35 height=39>

    .... 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

  13. #13
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: change cell color (office 2000)

    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.
    Legare Coleman

  14. #14
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Harrisburg, Pennsylvania, USA
    Posts
    168
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: change cell color (office 2000)

    Large,

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

    cvbs

  15. #15
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Harrisburg, Pennsylvania, USA
    Posts
    168
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: change cell color (office 2000)

    My bad,

    Thank you LEGUARE !,

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

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •