Page 1 of 2 12 LastLast
Results 1 to 15 of 28
  1. #1
    Lounger
    Join Date
    Apr 2004
    Posts
    29
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Conditional Formatting (XP)

    Hello,
    Excel provides only 3 levels of conditional formatting, I need more and I need to apply the formatting to a cell depending on value in different cell...How can I proceed?

    What I need is to change the font and/or background color of the specific cell in order to track delivery due dates (i.e: on time, on track, delayed, possible delay, arrived...)

    Thank you

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

    Re: Conditional Formatting (XP)

    You can use a custom number format to add some formatting options, but if you need a lot of different formatting, this is still too limited. You can use the Worksheet_Change event to change the formatting of a cell based on the value of another cell. See the attached crude example. Try to enter different numbers in cell B1 and see what happens in cell B3. You can right click the sheet tab ("Sheet1") and select View Code to inspect the code used for this.

  3. #3
    Lounger
    Join Date
    Apr 2004
    Posts
    29
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional Formatting (XP)

    Hello Hans,

    thank you for your reply, it works fine, but now how can I apply this to my situation: I have many items being tracked, each in different columns and the reference column for the formatting also changes, it is always 2 or 3 column away from the column I want to apply the formatting to.

    example: Column AC is the target column where I want to apply the formatting and column AE is where I will be gathering my info to apply my formatting. This goes on for other columns as well, for instance: AF relates to AH, AI to AK, and so on...but the relative moves are not constant...

    I don't know if it's any clear what I wrote...

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

    Re: Conditional Formatting (XP)

    You are either going to have to define a rule that the code can use to determine what cells need to be formatted and what cell they depend on, or you are going to have to come up with a table of cell addresses that need to formatted and the corresponding cells that determine the format. You will also need to define the rules that determine what formatting you want and what the formatting is for each rule. If you can do one of those and get back to us we can help write the necessary code.
    Legare Coleman

  5. #5
    Lounger
    Join Date
    Apr 2004
    Posts
    29
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional Formatting (XP)

    Ok, I have the column that need to be formatted and the column where I have to get the data in order to apply the formatting.
    Apply-to Condition
    AC AE
    AF AH
    AI AK
    AL AN
    AO AQ
    AR AT
    AU AW
    AX AZ
    BA BC
    BO BQ
    BT BV
    CD CF
    CJ CL
    CM CO
    CP CR
    CU CW
    CX CZ
    DA DM
    DO DR
    DT DW
    DY EB
    ED EF

    Condition value Apply-to Formatting
    0 White Font on Black Filling
    1 White Font on Red Filling

    the reason why I need this even though I have only 2 conditions, is becasue I already used the C.F. for other conditions in more than one column and I need to have them all standard so if I want to change the formatting applied accroding to one condition I don't have to go do it manually for the other columns where I already have some C.F. used.

    Thank you guys

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

    Re: Conditional Formatting (XP)

    Wow! Try this:

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range, cel As Range, intOffset As Integer
    Set rng = Intersect(Target, Union(Range("AE:AE"), Range("AH:AH"), _
    Range("AK:AK"), Range("AN:AN"), Range("AQ:AQ"), Range("AT:AT"), _
    Range("AW:AW"), Range("AZ:AZ"), Range("BC:BC"), Range("BQ:BQ"), _
    Range("BV:BV"), Range("CF:CF"), Range("CL:CL"), Range("CO:CO"), _
    Range("CR:CR"), Range("CW:CW"), Range("CZ:CZ"), Range("DMM"), _
    Range("DRR"), Range("DWW"), Range("EB:EB"), Range("EF:EF")))
    If Not rng Is Nothing Then
    For Each cel In rng
    Select Case cel.Column
    Case 117
    intOffset = -12
    Case 122, 127, 132
    intOffset = -3
    Case Else
    intOffset = -2
    End Select
    Select Case cel.Value
    Case ""
    cel.Offset(0, intOffset).Font.ColorIndex = xlColorIndexAutomatic
    cel.Offset(0, intOffset).Interior.ColorIndex = xlColorIndexNone
    Case 0
    cel.Offset(0, intOffset).Font.Color = vbWhite
    cel.Offset(0, intOffset).Interior.Color = vbBlack
    Case 1
    cel.Offset(0, intOffset).Font.Color = vbWhite
    cel.Offset(0, intOffset).Interior.Color = vbRed
    Case Else
    cel.Offset(0, intOffset).Font.ColorIndex = xlColorIndexAutomatic
    cel.Offset(0, intOffset).Interior.ColorIndex = xlColorIndexNone
    End Select
    Next cel
    End If
    Set cel = Nothing
    Set rng = Nothing
    End Sub

  7. #7
    Lounger
    Join Date
    Apr 2004
    Posts
    29
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional Formatting (XP)

    Hi Hans,

    I entered what you wrote but it doesn't work...it keeps giving me an error saying "type mismatch" and it highlights the Case "" line...it says <application-defined or object-defined error>

    I am trying to understand the code to figure out why it isn't working but I am not familiar enough to figure it out...

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

    Re: Conditional Formatting (XP)

    My code does not take error values into account. I have attached the workbook with slightly expanded code. If you still get errors, please try to find out when they occur.

  9. #9
    Lounger
    Join Date
    Apr 2004
    Posts
    29
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional Formatting (XP)

    Hi Hans,

    I made some modifications to my sheet and all the offset is now -2, how do I alter the code to take this into consideration?

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

    Re: Conditional Formatting (XP)

    You can replace this part

    Select Case cel.Column
    Case 117
    intOffset = -12
    Case 122, 127, 132
    intOffset = -3
    Case Else
    intOffset = -2
    End Select

    by

    intOffset = -2

    or you can even replace intOffset by -2 wherever it's used.

    (Conditional formatting would be a lot easier here)

  11. #11
    3 Star Lounger
    Join Date
    Jun 2002
    Location
    Duluth, Minnesota, USA
    Posts
    215
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional Formatting (XP)

    Hans,

    I have a similar need and wonder if you could help me as well?

    I have a table consisting of 7 columns by 10 rows (D12:J21). Depending on what happens elsewhere in th spreadsheet, an upper case X will be in one of the cells in the first row (D12:J12). I want the column with the X in it to retain "normal" foreground and background colors while the other cells in the other columns appear "greyed out" (or diminished in some way).

    I looked at your code a couple of posts up, but I am too new at VB to fully understand how to make this work. Can you help?

    Douglas

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

    Re: Conditional Formatting (XP)

    You don't need VBA code for this. Conditional formatting is sufficient:

    Select D12:J21 (D12 should be the active cell within the selection.)
    Select Format | Conditional Formatting...
    Under Condition1, select Formula Is.
    In the box next to it, enter the following formula:

    =(D$12="X")

    Click the Format... button.
    In the Color dropdown (in the Font tab), select one of the greys.
    Click OK twice.

    Note: the formula is for cell D12, but Excel will propagate it to the rest of the selection. The column will vary from D to J, but the $ in front of the row number 12 means that the formula will always refer to row 12, regardless of the row the formatted cell is in.

  13. #13
    3 Star Lounger
    Join Date
    Jun 2002
    Location
    Duluth, Minnesota, USA
    Posts
    215
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional Formatting (XP)

    Hans,

    Perfect. I did not know that Excel would propogate the conditional formatting down the column to the other cells. This worked just like I wanted. The only modification I had to make to your formula was to change the equal sign to a "does not equal" sign because I wanted the "greyed out" look to apply to those columns without the X.

    Thanks again. You guys rock.

    Douglas

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

    Re: Conditional Formatting (XP)

    Oops, sorry, that should have been NOT equal. Glad you solved it yourself.

  15. #15
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    Kontich, Vlaanderen, Belgium
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional Formatting (XP)

    Hans,

    I am trying to figure out how the code works in your "crude example" attached with <post#=401848>post 401848</post#>.
    I am not so familiar with VBA and want to ask you if you could explain your code to me.

    MTIA

    Regards,

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
  •