Page 1 of 3 123 LastLast
Results 1 to 15 of 35
  1. #1
    4 Star Lounger
    Join Date
    Apr 2002
    Location
    Northern Territory, Australia
    Posts
    471
    Thanks
    16
    Thanked 0 Times in 0 Posts

    Conditional Formatting 4 conditions (Excel 97)

    Hellor All

    I am trying to do a conditional format with 4 different conditions. Can this be done using conditional format or would it have to be vb?

    I want the cells D3:AG3 to show one of 4 different colours if AK

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional Formatting 4 conditions (Excel 97)

    You can add two more if you are willing to accept you can only change the colour of the font. Format the cells using this custom format string:

    [ red][=1]general;[ blue][=2]general;General

    then use the conditional format to colour 3 and 4

    NB: remove the space before RED and BLUE (I had to add them, otherwise the Lounge showed it like this:

    <font color=red>[=1]general;<font color=blue>[=2]general;General</font color=blue></font color=red>
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    4 Star Lounger
    Join Date
    Apr 2002
    Location
    Northern Territory, Australia
    Posts
    471
    Thanks
    16
    Thanked 0 Times in 0 Posts

    Re: Conditional Formatting 4 conditions (Excel 97)

    Hi Jan

    Changing the colour of the font isnt really desirable. I think the attached example will show better what I am trying to describe. If it isnt possible, it is not life threatening. I can live without it.

    Kerry

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional Formatting 4 conditions (Excel 97)

    Yup, that needs a macro. You might use the change event in the module behind the worksheet, checking for a change in column AK.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  5. #5
    4 Star Lounger
    Join Date
    Apr 2002
    Location
    Northern Territory, Australia
    Posts
    471
    Thanks
    16
    Thanked 0 Times in 0 Posts

    Re: Conditional Formatting 4 conditions (Excel 97)

    Any chance you could tell me how to do that?

    Kerry

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

    Re: Conditional Formatting 4 conditions (Excel 97)

    Activate the Visual Basic Editor (Alt+F11)
    Activate the Project Explorer (Ctrl+R)
    Double click the sheet (Sheet1 in your example)
    Enter or paste the following code:

    Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    Dim lngRow As Long, lngColorIndex As Long
    ' Test if cell that changed is in range AK2:AK9
    If Not (Intersect(Target, Range("AK2:AK9")) Is Nothing) Then
    ' Get row number
    lngRow = Target.Row
    ' Get color index
    Select Case Target.Value
    Case 1
    lngColorIndex = 36 ' yellow
    Case 2
    lngColorIndex = 34 ' pale blue
    Case 3
    lngColorIndex = 40 ' orange
    Case 4
    lngColorIndex = 15 ' gray
    Case Else
    lngColorIndex = -4142 ' transparent
    End Select
    ' Apply to columns C:AH in row
    Range("C" & lngRow & ":AH" & lngRow).Interior.ColorIndex = lngColorIndex
    End If
    End Sub

  7. #7
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts

    Re: Conditional Formatting 4 conditions (Excel 97)

    I looked at your file and have applied conditional formats to give what you want (see sheet 'rz' in attachment).
    Note: this will only work provided your AK column contains one of 1,2,3, or 4.
    You are only allowed up to 3 conditional formats so the trick is to set the default backround to one of your 4 conditions and then use the three allowed conditional formats for the other three.
    In the exmple attached, I set the default background colour to your '1' condition.
    This background will remain unless the column AK value is either 2, 3 or 4.

    zeddy

  8. #8
    4 Star Lounger
    Join Date
    Apr 2002
    Location
    Northern Territory, Australia
    Posts
    471
    Thanks
    16
    Thanked 0 Times in 0 Posts

    Re: Conditional Formatting 4 conditions (Excel 97)

    Thankyou Zeddy

    This is a simple resolution to the problem and works well.

    Regards Kerry

  9. #9
    4 Star Lounger
    Join Date
    Apr 2002
    Location
    Northern Territory, Australia
    Posts
    471
    Thanks
    16
    Thanked 0 Times in 0 Posts

    Re: Conditional Formatting 4 conditions (Excel 97)

    Thanks Hans

    I tried this but I couldnt get it to work. Not sure why? Did you get it to work on the sample?

    Kerry

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

    Re: Conditional Formatting 4 conditions (Excel 97)

    I have attached your workbook with the Worksheet_Change event handler. Try changing one of the entries in column AK.
    Note 1: This is a rough version just meant to demonstrate the idea. You should add error handling.
    Note 2: I removed the external links from the workbook.
    Note 3: If you are sure you'll never need more than 4 colors, Zeddy's method will suffice - it's simple and it works. But it can't be expanded to many colors, while the Woksheet_Change macro is flexible.

  11. #11
    4 Star Lounger
    Join Date
    Apr 2002
    Location
    Northern Territory, Australia
    Posts
    471
    Thanks
    16
    Thanked 0 Times in 0 Posts

    Re: Conditional Formatting 4 conditions (Excel 97)

    Hi Hans

    Once again you have a very good point. At this stage there are only 4 options, but there is no reason to say that in the future there may be more. I like the flexibility of the Macro. Once again I am learning to look at different options.

    I realised when I tried both solutions (yours and Zeddy's) that I have some other conditional formatting on the cells (C to AH) such as shading weekends, which I dont want to loose. Is it possible to edit your macro to make it refer to Columns A and B and AI to AK? I was able to do this with Zeddy's solution by just applying the conditional format to only those columns.

    Your help is much appreciated Hans.

    Regards

    Kerry

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

    Re: Conditional Formatting 4 conditions (Excel 97)

    If you want the shading to be applied to columns A, B and AI to AK, replace the instruction

    Range("C" & lngRow & ":AH" & lngRow).Interior.ColorIndex = lngColorIndex

    by these two:

    Range("A" & lngRow & ":B" & lngRow).Interior.ColorIndex = lngColorIndex
    Range("AI" & lngRow & ":AK" & lngRow).Interior.ColorIndex = lngColorIndex

  13. #13
    4 Star Lounger
    Join Date
    Apr 2002
    Location
    Northern Territory, Australia
    Posts
    471
    Thanks
    16
    Thanked 0 Times in 0 Posts

    Re: Conditional Formatting 4 conditions (Excel 97)

    THANKYOU! Hans.

    One more little question........... where do you get the numbers for different colours?

    Kerry

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

    Re: Conditional Formatting 4 conditions (Excel 97)

    Activate the Visual Basic Editor. In the online help index, type ColorIndex. If I remember correctly, you'll get a list of color swatches with corresponding numbers.

    Otherwise, select a cell with the desired shading, then activate the Visual Basic Editor (Alt+F11), open the Immediate window (Ctrl+G), type ? ActiveCell.Interior.ColorIndex and press Enter.

  15. #15
    4 Star Lounger
    Join Date
    Apr 2002
    Location
    Northern Territory, Australia
    Posts
    471
    Thanks
    16
    Thanked 0 Times in 0 Posts

    Re: Conditional Formatting 4 conditions (Excel 97)

    How can I make this Macro apply to 12 worksheets in my workbook (one for each month) without putting the macro in for each sheet?

    Kerry

Page 1 of 3 123 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
  •