How can I conditional format cells with more than 3 conditio
Power Query Course in Spanish
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: How can I conditional format cells with more than 3 conditio

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

    Default

     
    How would I conditionally format a group of 5 cells on the basis that one of the 5 cells is equal to any number of 10 possible conditions.

    eg. If cell 3 of cells 1-5 is = x, then cell is light green background, but if y, z, a, b, c, d, e, f... it is another colour.

    Need a macro?

    Any help appreciated.
    garth

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi. This is one way you can do it
    Edit to suit
    This code will change the interior color of the cells A1:A5 if the value entered is 1 or 2. You can add as many Case statements as you wish and format the cell any way you wish. For colors you can use ColorIndex or RGB.. If you want the format reset when a value is removed, you will have to add code for that as well... See help files. Have Fun!

    Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Column = 1 And Target.Row < 6 Then
    Select Case Target.Value
    Case 1
    Target.Interior.ColorIndex = 3
    Case 2
    Target.Interior.ColorIndex = 3
    End Select
    End If
    End Sub

  3. #3
    New Member
    Join Date
    Mar 2002
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Advice appreciated, though I am still a little lost.

    Some more background... I am doing a map of a floor plan in which there are multiple 5rowx2col locations. I want to, on the basis of one of the cells within each of these locations, give every cell for each location a particular background color.

    Unfortunately these locations are not in any uniform order because of the floor plan! Is there anyway to set up a loop that will look for each of the locns??

    rgds

  4. #4
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I think i understand what you are saying.
    When a certain value is entered into a cell then the whole group to which that cell belongs changes to a certain color???

    In continuing, the groups however are different and random???

    If this is the case, then it is beyond my scope.

    Sorry

    Good Luck


  5. #5
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Are these floor plans which will be reused??

    Anyway, you can define ranges and then test the active cell relative to a "Group" you have predefined.

    If this is your goal then please send me info on two of your groups, the color for each, the value(s) to watch for, and in which or all cells in it's particular group...

    You can test for any position/selection and/or value.
    So it seems to me that your floor plan is very doable.

    Repost Here or send me an e-Mail

    tstom@hotmail.com

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

    Default

      
    Hi garth

    This might give you some ideas
    In each cell in area 1 type the formula = $A$1
    In each cell in area 2 type the formula =$B$2
    etc etc
    Right click sheet tab, left click View Code and paste in this code

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Row = 1 Then
    On Error Resume Next
    For Each cell In Range("A1:T50")
    cell.Interior.ColorIndex = cell.Value
    If cell.Row <> 1 Then cell.Font.ColorIndex = cell.Value
    Next
    End If
    End Sub

    Change A1:T50 to the area that covers your floor plan

    Now when you enter a number in A1 that cell and all cells in area 1 will change
    Now when you enter a number in B1 that cell and all cells in area 2 will change
    The line….If Target Row = 1…..ensures this macro only runs if you make an entry in row 1
    The line …if cell.Row <> 1….ensures you can still read the number you type in row 1
    to enable you to see what number relates to which color

    Good luck
    regards
    Derek


    [ This Message was edited by: Derek on 2002-03-22 01:17 ]

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
  •  

 

 
DMCA.com