Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: Question about conditional formatting

  1. #1
    New Member
    Join Date
    Jan 2003
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hello,

    With the option Conditional format it is very easy to give cells a different
    format based on their data, however it is only possible to set three conditions.
    Is there an easy way to e.g. create 5 different formats (in my case 5 different colors) based on data in one column?

    The other way around: is it also possible to do some action on cells which have specific colors (e.g. if Red, than count these cells)? is this possible via a standard function within Excel or do I need to use VBA for achieving this.

    Thanks!
    Jim van de Berg

  2. #2
    MrExcel MVP
    Join Date
    Apr 2002
    Location
    Vancouver BC , Canada
    Posts
    6,259
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I feel that the "easiest" way to deal with both problems is to use VBA.

    Heres a quick example of VBA code to change cell color relative to cell value.

    Private Sub Worksheet_Change(ByVal Target As Range)
    With Target.Interior

    Select Case Target.Value


    Case 1
    .ColorIndex = 6
    .Pattern = xlSolid

    Case 20 To 30
    .ColorIndex = 3
    .Pattern = xlSolid

    Case Else
    .ColorIndex = 0
    .Pattern = xlSolid

    End Select
    End With
    End Sub

    EXPLAINATION:
    if cell value = 1 then make cell yellow
    if cell value = 20-30 then make red
    For any other value don't use color

    Note: to use code just :
    1) rite click on Tab of Sheet you want to use
    2) left click on "view code"
    3) paste code into the VBA editor "Code window"
    4) boomba ... done

    VBA ADVANTAGE: no limit to how many conditions are tested for !

    MORE INFO ON "SELECT-CASE"
    example from VBA help file:

    Select Case Statement Example
    This example uses the Select Case statement to evaluate the value of a variable. The second Case clause contains the value of the variable being evaluated, and therefore only the statement associated with it is executed.

    Dim Number
    Number = 8 ' Initialize variable.
    Select Case Number ' Evaluate Number.
    Case 1 To 5 ' Number between 1 and 5, inclusive.
    Debug.Print "Between 1 and 5"
    ' The following is the only Case clause that evaluates to True.
    Case 6, 7, 8 ' Number between 6 and 8.
    Debug.Print "Between 6 and 8"
    Case 9 To 10 ' Number is 9 or 10.
    Debug.Print "Greater than 8"
    Case Else ' Other values.
    Debug.Print "Not between 1 and 10"
    End Select




    _________________
    NOTE: (Testing performed on Win2K utilizing Office 2000. Solutions may need tweaking for other versions.)
    Adieu,Nimrod
    [ This Message was edited by: Nimrod on 2003-01-26 13:38 ]

    [ This Message was edited by: Nimrod on 2003-01-26 13:42 ]

    [ This Message was edited by: Nimrod on 2003-01-26 13:51 ]

  3. #3
    Board Regular
    Join Date
    Jun 2002
    Location
    Perth, Australia
    Posts
    1,416
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    You can get up to 6 font colours with a combination of Conditional Formatting (3) and Format/Cells/Number/Custom:

    For example:

    < 0 Red
    0 - 10 Green
    11 - 20 Blue
    21 - 30 Orange
    31 - 40 Light Purple
    + 40 Black



    Use Custom Formatting for < 0, 0 - 10 and + 40:

    [Red][<0](#,##0.00);[Green][<10]0.00_);#,##0.00_);@

    Use Conditional Formatting for:

    Cell value between 11 - 20
    Cell value between 21 - 30
    Cell value between 31 - 40

    You can use the SUMPRODUCT function to count the number of cells with a specific value(s). This function does not count colours as such but can be used to count the values in the cell e.g. count all cells in the range named DataRange that are > 0 and less than or equal to 31:

    =SUMPRODUCT(((B44:B49)<=31)*((B44:B49)>0))


    HTH

    Mike

    [ This Message was edited by: Ekim on 2003-01-27 00:45 ]

  4. #4
    New Member
    Join Date
    Jan 2003
    Posts
    34
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Question about conditional formatting

    How do you change the format of a range of cells when using your Case statement?

    I usually have formulas in my Conditional Format that set the entire row based on a value change from one column.

    For example:

    I use a formula like this: =FIND("T:",$B2,1)=1

    It will then set the entire range (A2:F2) to a color I specify.


    How do I tell VBA to use the range?

  5. #5
    New Member
    Join Date
    Jun 2003
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Question about conditional formatting

    This may have been answered above.... but I couldn't really tell.

    I need to copy cells that are red to another worksheet. If they are not red, no copy. Is there anyway to do that?

    Thank you,

    Matt

  6. #6
    Board Regular
    Join Date
    Feb 2003
    Posts
    508
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Question about conditional formatting

    Hi everybody !

    In the following table i need to set conditional formatting in C9:
    if cell value = D5 (color in red)
    if cell value = E5 ( color in blue)
    if cell value = F5 (color in yellow)
    if cell value = G5 (color in orange)
    if cell value = H5 (color in teal)

    which means more than 3 criteria.

    I tried all the answers given in this thread and others and couldnt solve , is it possible ???

    Thank you !

    ******** ******************** ************************************************************************>
    Microsoft Excel - Book2___Running: 11.0 : OS = Windows Windows 2000
    (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
    =

    B
    C
    D
    E
    F
    G
    H
    I
    1
    2
    3
    RANK12345
    4
    75431
    5
    TYPEEBCAD
    6
    7
    8
    TYPE
    9
    A3
    10
    B5
    11
    C4
    12
    D1
    13
    E7
    14
    15
    Sheet1

    [HtmlMaker 2.41] To see the formula in the cells just click on the cells hyperlink or click the Name box
    PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
    He who asks a question is a fool for five minutes; he who does not ask a question remains a fool forever.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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