Results 1 to 11 of 11
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    make green cells (excel xp)

    I have a spreadsheet with cells C, E, G, I (every other column) in rows 1 to 25 that may have a number in it from 1 to 6. If it's 1 i need to put green, if it's 2 i need to put red, etc.

    I'm adding a command button and want it to look through this range and then make the cell fill with that color and make the value in that cell the same color.

    I've attached a test.xls where i did 2 of the numbers in range("C5") and ("E5") but i need to go through a range of like 25 rows and 12 columns. Thank you
    Attached Files Attached Files

  2. #2
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Lawrence, Kansas, USA
    Posts
    202
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: make green cells (excel xp)

    I'm not quite sure what cell you want to be green or red. The example shows the cell above the number. Whichever cell it is, it sounds like a job for a conditional format. I've added a conditional format that you can see and change however you'd like.
    Attached Files Attached Files

  3. #3
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: make green cells (excel xp)

    yes, but what do i do when i have number 1 through 6. I can only do 3 in conditional formatting so i need a for/next statement to iterate through all the cells and all the rows to look for a 1,2,3,4,5 or 6 and make it the proper color.

    for cells
    if cell.value = 1 then make it green
    if cell.value = 2 make it red
    if cell.value = 3 make it blue
    ...
    next cell

    i don't know how to move through every "other" column and then go down to the next row.

    thanks

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: make green cells (excel xp)

    Try the following which utilises the Union method : <pre>Sub ColorCells()
    Dim colorRange As Range, oCell As Range
    Set colorRange = Union([C1:C25], [E1:E25], [G1:G25], [I1:I25])
    For Each oCell In colorRange
    Select Case oCell.Value
    Case 1
    oCell.Interior.ColorIndex = 10
    oCell.Font.ColorIndex = 10
    Case 2
    oCell.Interior.ColorIndex = 3
    oCell.Font.ColorIndex = 3
    Case 3
    'etc
    End Select
    Next
    End Sub</pre>


    Andrew C

  5. #5
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: make green cells (excel xp)

    Actually you can use an array instead of a select case, as follows:<pre>Sub ColorCells()
    Dim colorRange As Range, oCell As Range
    Dim intColors()
    intColors = Array(10, 3, 6, 20, 42, 8)
    Set colorRange = Union([C1:C25], [E1:E25], [G1:G25], [I1:I25])
    For Each oCell In colorRange
    With oCell
    .Interior.ColorIndex = intColors(.Value - 1)
    .Font.ColorIndex = intColors(.Value - 1)
    End With
    Next
    End Sub</pre>


    Excuse my random unviewed selection of colors

    Andrew C

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

    Re: make green cells (excel xp)

    Try this. The code is in the worksheet change event.
    Attached Files Attached Files
    Legare Coleman

  7. #7
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: make green cells (excel xp)

    Thank you. Could you tell me what the Union command is. I've never seen that one before. thanks

  8. #8
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: make green cells (excel xp)

    The Union method combines two or more ranges into a single object. It is not required that the ranges be adjacent or contiguous.

    Andrew C

  9. #9
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: make green cells (excel xp)

    Intersect method is also a good one to know. You 'll see a few posts using something like this example:

    Set myRange = Intersect(ActiveSheet.UsedRange, Selection)

    which helps to cut the range object size down for whatever action, if the user has selected entire columns or the entire worksheet
    -John ... I float in liquid gardens
    UTC -7ąDS

  10. #10
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: make green cells (excel xp)

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim oCell As Range
    With ActiveSheet
    If Intersect(Target, Union(.Range("C20:C75"), .Range("E20:E25"), .Range("G20:G75"), _
    .Range("I20:I75"), .Range("K20:K75"), .Range("M20:M75"), .Range("O20:O75"), .Range("Q20:Q75"), _
    .Range("S20:S75"), .Range("U20:U75"), .Range("W20:W75"), .Range("Y20:Y75"))) Is Nothing Then Exit Sub
    For Each oCell In Intersect(Target, Union(.Range("C20:C75"), .Range("E20:E75"), _
    .Range("G20:G75"), .Range("I20:I75"), .Range("K20:K75"), .Range("M20:M75"), .Range("O20:O75"), .Range("Q20:Q75"), _
    .Range("S20:S75"), .Range("U20:U75"), .Range("W20:W75"), .Range("Y20:Y75")))
    With oCell.Interior
    Select Case oCell.Value
    Case 1
    .ColorIndex = 38
    oCell.Font.ColorIndex = 38
    Case 2
    .ColorIndex = 40
    oCell.Font.ColorIndex = 40
    Case 3
    .ColorIndex = 36
    oCell.Font.ColorIndex = 36
    Case 4
    .ColorIndex = 5
    oCell.Font.ColorIndex = 5
    Case 5
    .ColorIndex = 37
    oCell.Font.ColorIndex = 37
    Case 6
    .ColorIndex = 16
    oCell.Font.ColorIndex = 16
    Case Else
    .ColorIndex = xlColorIndexNone
    oCell.Font.ColorIndex = xlColorIndexAutomatic
    End Select
    End With
    Next oCell
    End With
    End Sub


    This code works fine but for some reason if i click around the ranges and put numbers in and change them, it intermittently stops working. It will only work again if i take out all formatting from that cell by using edit/clear/formats. Do you know what cause that? thakns

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

    Re: make green cells (excel xp)

    I see only one thing wrong with your code. In the If statement, you have .Range("E20:E25") where I think that you want .Range("E20:E75"). That would cause the code to not work for cells E26:E75. However, clearing formats would not change that.

    I don't see anything that would cause the symptom you describe, and I can not get it to fail on my system.
    Legare Coleman

Posting Permissions

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