Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    2 Star Lounger
    Join Date
    Oct 2001
    Location
    Not in KC anymore
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Format cells based on other cell (2007)

    Hello,

    I need a way to format a group of cells (a partial row) based on criteria in a separate cell, that will not be part of the formatted group.

    For example,

    If cell H1 is equal to 5, then I need cells A1-G1 to be shaded red.

    I've played around with some conditional formatting, which I can get to format the cells, however this doesn't do what I need. I need a way to overwrite the formatting once it's been formatting. (I may decide I want to shade it blue, which I can't if it meets the condition.)

    And I need it to not be dependent on a macro because I will distribute the spreadsheet, and don't want macros as part of the sheet. I'm OK using a macro to get what I need...I basically need it to do its job and be done with it.

    I've performed some searches and haven't found what I need. If it's already been asked, forgive me and please point me in the right direction.

    Thanks!

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

    Re: Format cells based on other cell (2007)

    If a cell is shaded red because of conditional formatting, you cannot override this by applying blue shading manually - conditional formatting has precedence above manual formatting. If you want to keep the conditional formatting, you must find a way to apply the blue shading through conditional formatting too. When doing this, keep in mind that Excel evaluates the conditions one by one, starting at the top one. When a condition is met, the corresponding formatting is applied and the rest of the conditions is ignored - they aren't even evaluated any more (for that cell).

  3. #3
    2 Star Lounger
    Join Date
    Oct 2001
    Location
    Not in KC anymore
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Format cells based on other cell (2007)

    Yeah, conditional formatting is not the route for me on this.

    Is there a way to have code examine the cell and then shade the cells "manually" without using conditions?

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

    Re: Format cells based on other cell (2007)

    Yes, taking your example

    > If cell H1 is equal to 5, then I need cells A1-G1 to be shaded red.

    you could use this macro:

    Sub ColorMe
    If Range("H1") = 5 Then
    Range("A1:G1").Interior.Color = vbRed
    End If
    End Sub

    You can add other If ... End If blocks to the macro.

  5. #5
    2 Star Lounger
    Join Date
    Oct 2001
    Location
    Not in KC anymore
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Format cells based on other cell (2007)

    Sweet!

    I'm trying to make it run down the entire sheet and do the same thing to each row that meets the criteria, but I'm missing something.

    Sub ColorMe()
    Dim cell As Range
    For Each cell In ActiveSheet.Columns("W").Cells.SpecialCells(xlCell TypeConstants)
    If cell.Value > 0 Then
    Range("A:X").Interior.Color = vbRed
    End If
    Next cell
    End Sub

    I know I'm missing something on the range part...the entire columns are all red.

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

    Re: Format cells based on other cell (2007)

    Range("A:X") refers to the entire columns A through X. Change the line

    Range("A:X").Interior.Color = vbRed

    to

    Range("A" & cell.Row & ":X" & cell.Row).Interior.Color = vbRed

  7. #7
    2 Star Lounger
    Join Date
    Oct 2001
    Location
    Not in KC anymore
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Format cells based on other cell (2007)

    Oh cool...this is great.

    I'm really trying to do this myself, but I'm running into some problems when I make it more complex.

    I'd like to shade the cells based on a field containing the time and date. If the time is greater than 17:00, then I need it to shade a certain color. I've looked around and can't find any way to extract the time in VB code. ( I can get it to extract into another cell, then base the shading on that, but I'd like to keep it all contained in the code).

    Also, is there a way to shade the row if the criteria is met for more than one cell? If A1 = No and A2>0, then I need it to be shaded.

    I'll keep messing and searching. Thanks for all the assistance. It is much appreciated.

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

    Re: Format cells based on other cell (2007)

    You can use Hour(Range("K1")) to extract the hour part of the date/time value in cell K1, so you can test for

    If Hour(Range("K1")) >= 17 Then

    If you want to test that two conditions are met, you can use

    If Condition1 And Condition2 Then

    For example

    If Range("A1") = "No" And Range("B1") > 0 Then

  9. #9
    2 Star Lounger
    Join Date
    Oct 2001
    Location
    Not in KC anymore
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Format cells based on other cell (2007)

    Thanks for the help.

    I got the time extraction working for me...thanks!

    For Each cell In ActiveSheet.Range("P2:P300").Cells.SpecialCells(xl CellTypeConstants)
    If Hour(cell.Value) >= 17 Then
    Range("A" & cell.Row & ":X" & cell.Row).Interior.ColorIndex = 34
    End If
    Next cell


    I'm having trouble with the two conditions. I understand "If Range("A1") = "No" And Range("B1") > 0 Then" and how it works. I'm having trouble having it run down the list and looking at both criteria. What you provided will look only at the specific cells, not the ones in the for/next loop.

    Here's what I've tried. Which one is close and can you help me out some more?

    For Each cell In ActiveSheet.Range("R2:S300").Cells.SpecialCells(xl CellTypeConstants)
    If Range(cell.Value) = "No" And Range(cell.Value) > 0 Then

    Range("A" & cell.Row & ":X" & cell.Row).Font.ColorIndex = 3
    End If
    Next cell


    Set Condition1 = Range("R2:R300")
    Set Condition2 = Range("S2:S300")
    For Each cell In ActiveSheet.Range("R2:S300").Cells.SpecialCells(xl CellTypeConstants)
    If Condition1 >= "No" And Condition2 > 0 Then
    Range("A" & cell.Row & ":X" & cell.Row).Font.ColorIndex = 3
    End If
    Next cell


    I'll keep trying in the meantime.

    Thanks a lot!

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

    Re: Format cells based on other cell (2007)

    Try this slightly different approach:
    <code>
    Dim n As Long
    For n = 2 To 300
    If Range("R" & n) = "No" And Range("S" & n) > 0 Then
    Range("A" & n & ":X" & n).Font.ColorIndex = 3
    End If
    Next n</code>

  11. #11
    2 Star Lounger
    Join Date
    Oct 2001
    Location
    Not in KC anymore
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Format cells based on other cell (2007)

    Hey thanks. That did what I needed it to do.

    The more I learn about this, the more I realize I don't know. <img src=/S/drop.gif border=0 alt=drop width=23 height=23>

    Would I be able to use this approach to do something with times too? If I want to find a time after 7:00 in column O and after 23:00 in column P, can I use something like this?

    Dim n As Long
    For n = 2 To 300
    If Hour("O" & n) >= 7 And Hour("P" & n) <= 23 Then
    Range("A" & n & ":X" & n).Interior.ColorIndex = 3
    End If
    Next n

    I try it and get a mismatch error.

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

    Re: Format cells based on other cell (2007)

    You write "and after 23:00 in column P" but from your proposed code I assume that you mean "and before 23:00 in column P". If so, the code looks OK.

    Are you sure that ALL cells in columns O2:P300 contain date/time values?

  13. #13
    2 Star Lounger
    Join Date
    Oct 2001
    Location
    Not in KC anymore
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Format cells based on other cell (2007)

    I meant to write before. Whoops.

    Not every cell has a date/time value. The ones that don't are blank. And there won't always be the same number of cells to review. I have 300 just to cover everything. There are usually between 200 and 300 rows. Never more. Sometimes less.

    I modified the spreadsheet to have only date/time values, and specified in the code the exact range which contained date/time values. I still ran into the same error.

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

    Re: Format cells based on other cell (2007)

    Could you attach (a stripped down copy of) the workbook to a reply? Replace sensitive information with dummy data.

  15. #15
    2 Star Lounger
    Join Date
    Oct 2001
    Location
    Not in KC anymore
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Format cells based on other cell (2007)

    Try this.

    Thanks!
    Attached Files Attached Files

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
  •