Consulting

Results 1 to 9 of 9

Thread: Solved: Changing Cell Fill Color Depending On Its Contents

  1. 01-17-2006, 10:02 PM #1
    zoom38
    zoom38 is offline
    VBAX Mentor
    Joined
    Jan 2006
    Posts
    323
    Location

    Solved: Changing Cell Fill Color Depending On Its Contents

    I have a worksheet in which I need b8:o31 to automatically change the fill color based on its contents. I tried conditional format but that only allows for 3 conditions. I will need approximately 70 conditions that need to be addressed. If I can use wildcard characters I could narrow it down to about 10 conditions. Can wildcard characters be used in a conditional format? I tried but with no success. I did a search on VBA Express and on the internet but could not find what I need. Can you guys lead me in the right direction.

    Thanks
    Gary
    Reply With Quote Reply With Quote
  2. 01-17-2006, 11:14 PM #2
    Norie
    Norie is offline
    VBAX Master Norie's Avatar
    Joined
    Jan 2005
    Location
    Stirling, Scotland
    Posts
    1,831
    Location
    Gary

    Could you give some more information on your conditions?
    Reply With Quote Reply With Quote
  3. 01-18-2006, 12:17 AM #3
    Ken Puls
    Ken Puls is offline
    Moderator VBAX Guru Ken Puls's Avatar
    Joined
    Aug 2004
    Location
    Nanaimo, BC, Canada
    Posts
    4,001
    Location
    Hi Gary,

    You could try this. The code must go in a worksheet module. Find the sheet you want this to run on, right click it, click view code, and paste this in the module that shows up:

    [vba]Private Sub Worksheet_Change(ByVal Target As Range)
    Select Case Target.Value
    Case Is = 1
    Target.Interior.ColorIndex = 6
    Case Is = 2
    Target.Interior.ColorIndex = 23
    Case Is = "Hello"
    Target.Interior.ColorIndex = 35
    Case Else
    'No conditions met, so make it normal
    Target.Interior.ColorIndex = -4142
    End Select

    End Sub[/vba]

    Just add a case for each scenario.

    HTH,
    Ken Puls, CMA - Microsoft MVP (Excel)
    I hate it when my computer does what I tell it to, and not what I want it to.

    Learn how to use our KB tags! -||- Ken's Excel Website -||- Ken's Excel Forums -||- My Blog -||- Excel Training Calendar

    This is a shameless plug for my new book "RibbonX - Customizing the Office 2007 Ribbon". Find out more about it here!

    Help keep VBAX clean! Use the 'Thread Tools' menu to mark your own threads solved!




    Reply With Quote Reply With Quote
  4. 01-18-2006, 06:08 PM #4
    zoom38
    zoom38 is offline
    VBAX Mentor
    Joined
    Jan 2006
    Posts
    323
    Location
    Ken that worked nicely but it is quite long. Is there a way to shorten it up? Can I use wildcard characters? If I could that would shorten it up significantly. If you look at the code you will understand what I mean.
    [VBA]Private Sub Worksheet_Change(ByVal Target As Range)
    Select Case Target.Value
    Case Is = "V.5"
    Target.Interior.ColorIndex = 35
    Case Is = "V1"
    Target.Interior.ColorIndex = 35
    Case Is = "V1.5"
    Target.Interior.ColorIndex = 35
    Case Is = "V2"
    Target.Interior.ColorIndex = 35
    Case Is = "V2.5"
    Target.Interior.ColorIndex = 35
    Case Is = "V3"
    Target.Interior.ColorIndex = 35
    Case Is = "V3.5"
    Target.Interior.ColorIndex = 35
    Case Is = "V4"
    Target.Interior.ColorIndex = 35
    Case Is = "V4.5"
    Target.Interior.ColorIndex = 35
    Case Is = "V5"
    Target.Interior.ColorIndex = 35
    Case Is = "V5.5"
    Target.Interior.ColorIndex = 35
    Case Is = "V6"
    Target.Interior.ColorIndex = 35
    Case Is = "V6.5"
    Target.Interior.ColorIndex = 35
    Case Is = "V7"
    Target.Interior.ColorIndex = 35
    Case Is = "V7.5"
    Target.Interior.ColorIndex = 35
    Case Is = "V8"
    Target.Interior.ColorIndex = 35
    Case Is = "V8.5"
    Target.Interior.ColorIndex = 35
    Case Is = "V9"
    Target.Interior.ColorIndex = 35
    Case Is = "V9.5"
    Target.Interior.ColorIndex = 35
    Case Is = "V10"
    Target.Interior.ColorIndex = 35
    Case Is = "V10.5"
    Target.Interior.ColorIndex = 35
    Case Is = "V11"
    Target.Interior.ColorIndex = 35
    Case Is = "V11.5"
    Target.Interior.ColorIndex = 35
    Case Is = "V12"
    Target.Interior.ColorIndex = 35
    Case Is = "PL.5"
    Target.Interior.ColorIndex = 34
    Case Is = "PL1"
    Target.Interior.ColorIndex = 34
    Case Is = "PL1.5"
    Target.Interior.ColorIndex = 34
    Case Is = "PL2"
    Target.Interior.ColorIndex = 34
    Case Is = "PL2.5"
    Target.Interior.ColorIndex = 34
    Case Is = "PL3"
    Target.Interior.ColorIndex = 34
    Case Is = "PL3.5"
    Target.Interior.ColorIndex = 34
    Case Is = "PL4"
    Target.Interior.ColorIndex = 34
    Case Is = "PL4.5"
    Target.Interior.ColorIndex = 34
    Case Is = "PL5"
    Target.Interior.ColorIndex = 34
    Case Is = "PL5.5"
    Target.Interior.ColorIndex = 34
    Case Is = "PL6"
    Target.Interior.ColorIndex = 34
    Case Is = "PL6.5"
    Target.Interior.ColorIndex = 34
    Case Is = "PL7"
    Target.Interior.ColorIndex = 34
    Case Is = "PL7.5"
    Target.Interior.ColorIndex = 34
    Case Is = "PL8"
    Target.Interior.ColorIndex = 34
    Case Is = "PL8.5"
    Target.Interior.ColorIndex = 34
    Case Is = "PL9"
    Target.Interior.ColorIndex = 34
    Case Is = "PL9.5"
    Target.Interior.ColorIndex = 34
    Case Is = "PL10"
    Target.Interior.ColorIndex = 34
    Case Is = "PL10.5"
    Target.Interior.ColorIndex = 34
    Case Is = "PL11"
    Target.Interior.ColorIndex = 34
    Case Is = "PL11.5"
    Target.Interior.ColorIndex = 34
    Case Is = "PL12"
    Target.Interior.ColorIndex = 34
    Case Is = "SL.5"
    Target.Interior.ColorIndex = 36
    Case Is = "SL1"
    Target.Interior.ColorIndex = 36
    Case Is = "SL1.5"
    Target.Interior.ColorIndex = 36
    Case Is = "SL2"
    Target.Interior.ColorIndex = 36
    Case Is = "SL2.5"
    Target.Interior.ColorIndex = 36
    Case Is = "SL3"
    Target.Interior.ColorIndex = 36
    Case Is = "SL3.5"
    Target.Interior.ColorIndex = 36
    Case Is = "SL4"
    Target.Interior.ColorIndex = 36
    Case Is = "SL4.5"
    Target.Interior.ColorIndex = 36
    Case Is = "SL5"
    Target.Interior.ColorIndex = 36
    Case Is = "SL5.5"
    Target.Interior.ColorIndex = 36
    Case Is = "SL6"
    Target.Interior.ColorIndex = 36
    Case Is = "SL6.5"
    Target.Interior.ColorIndex = 36
    Case Is = "SL7"
    Target.Interior.ColorIndex = 36
    Case Is = "SL7.5"
    Target.Interior.ColorIndex = 36
    Case Is = "SL8"
    Target.Interior.ColorIndex = 36
    Case Is = "SL8.5"
    Target.Interior.ColorIndex = 36
    Case Is = "SL9"
    Target.Interior.ColorIndex = 36
    Case Is = "SL9.5"
    Target.Interior.ColorIndex = 36
    Case Is = "SL10"
    Target.Interior.ColorIndex = 36
    Case Is = "SL10.5"
    Target.Interior.ColorIndex = 36
    Case Is = "SL11"
    Target.Interior.ColorIndex = 36
    Case Is = "SL11.5"
    Target.Interior.ColorIndex = 36
    Case Is = "SL12"
    Target.Interior.ColorIndex = 36
    Case Is = "FS.5"
    Target.Interior.ColorIndex = 36
    Case Is = "FS1"
    Target.Interior.ColorIndex = 36
    Case Is = "FS1.5"
    Target.Interior.ColorIndex = 36
    Case Is = "FS2"
    Target.Interior.ColorIndex = 36
    Case Is = "FS2.5"
    Target.Interior.ColorIndex = 36
    Case Is = "FS3"
    Target.Interior.ColorIndex = 36
    Case Is = "FS3.5"
    Target.Interior.ColorIndex = 36
    Case Is = "FS4"
    Target.Interior.ColorIndex = 36
    Case Is = "FS4.5"
    Target.Interior.ColorIndex = 36
    Case Is = "FS5"
    Target.Interior.ColorIndex = 36
    Case Is = "FS5.5"
    Target.Interior.ColorIndex = 36
    Case Is = "FS6"
    Target.Interior.ColorIndex = 36
    Case Is = "FS6.5"
    Target.Interior.ColorIndex = 36
    Case Is = "FS7"
    Target.Interior.ColorIndex = 36
    Case Is = "FS7.5"
    Target.Interior.ColorIndex = 36
    Case Is = "FS8"
    Target.Interior.ColorIndex = 36
    Case Is = "FS8.5"
    Target.Interior.ColorIndex = 36
    Case Is = "FS9"
    Target.Interior.ColorIndex = 36
    Case Is = "FS9.5"
    Target.Interior.ColorIndex = 36
    Case Is = "FS10"
    Target.Interior.ColorIndex = 36
    Case Is = "FS10.5"
    Target.Interior.ColorIndex = 36
    Case Is = "FS11"
    Target.Interior.ColorIndex = 36
    Case Is = "FS11.5"
    Target.Interior.ColorIndex = 36
    Case Is = "FS12"
    Target.Interior.ColorIndex = 36
    Case Is = "ML.5"
    Target.Interior.ColorIndex = 24
    Case Is = "ML1"
    Target.Interior.ColorIndex = 24
    Case Is = "ML1.5"
    Target.Interior.ColorIndex = 24
    Case Is = "ML2"
    Target.Interior.ColorIndex = 24
    Case Is = "ML2.5"
    Target.Interior.ColorIndex = 24
    Case Is = "ML3"
    Target.Interior.ColorIndex = 24
    Case Is = "ML3.5"
    Target.Interior.ColorIndex = 24
    Case Is = "ML4"
    Target.Interior.ColorIndex = 24
    Case Is = "ML4.5"
    Target.Interior.ColorIndex = 24
    Case Is = "ML5"
    Target.Interior.ColorIndex = 24
    Case Is = "ML5.5"
    Target.Interior.ColorIndex = 24
    Case Is = "ML6"
    Target.Interior.ColorIndex = 24
    Case Is = "ML6.5"
    Target.Interior.ColorIndex = 24
    Case Is = "ML7"
    Target.Interior.ColorIndex = 24
    Case Is = "ML7.5"
    Target.Interior.ColorIndex = 24
    Case Is = "ML8"
    Target.Interior.ColorIndex = 24
    Case Is = "ML8.5"
    Target.Interior.ColorIndex = 24
    Case Is = "ML9"
    Target.Interior.ColorIndex = 24
    Case Is = "ML9.5"
    Target.Interior.ColorIndex = 24
    Case Is = "ML10"
    Target.Interior.ColorIndex = 24
    Case Is = "ML10.5"
    Target.Interior.ColorIndex = 24
    Case Is = "ML11"
    Target.Interior.ColorIndex = 24
    Case Is = "ML11.5"
    Target.Interior.ColorIndex = 24
    Case Is = "ML12"
    Target.Interior.ColorIndex = 24
    Case Else
    'No conditions met, so make it normal
    Target.Interior.ColorIndex = -4142
    End Select

    End Sub
    [/VBA]

    Also how do I make it so it will work on the entire workbook not just the worksheet?

    Thanks
    Gary
    Reply With Quote Reply With Quote
  5. 01-18-2006, 09:32 PM #5
    geekgirlau
    geekgirlau is offline
    Moderator VBAX Master geekgirlau's Avatar
    Joined
    Aug 2004
    Location
    Melbourne, Australia
    Posts
    1,464
    Location
    [VBA]
    Select Case Target.Value
    Case "V.5", "V1" To "V999"
    Target.Interior.ColorIndex = 35

    Case "PL.5", "PL1" To "PL999"
    Target.Interior.ColorIndex = 34

    Case "SL.5", "SL1" To "SL999", "FS.5", "FS1" To "FS999"
    Target.Interior.ColorIndex = 36

    Case Is = "ML.5", "ML1" To "ML999"
    Target.Interior.ColorIndex = 24

    Case Else
    'No conditions met, so make it normal
    Target.Interior.ColorIndex = -4142
    End Select
    [/VBA]

    Make it part of the workbook SheetChange event if you want it to occur on all sheets.
    Reply With Quote Reply With Quote
  6. 01-19-2006, 04:27 PM #6
    zoom38
    zoom38 is offline
    VBAX Mentor
    Joined
    Jan 2006
    Posts
    323
    Location
    Once again you guys & gals came through. Problem solved.
    Thanks
    Gary
    Reply With Quote Reply With Quote
  7. 01-20-2006, 04:42 PM #7
    Ken Puls
    Ken Puls is offline
    Moderator VBAX Guru Ken Puls's Avatar
    Joined
    Aug 2004
    Location
    Nanaimo, BC, Canada
    Posts
    4,001
    Location
    Just for fun, here's another way to skin it:

    [vba]Select Case Left(Target.Value,2)

    Case "PL"
    Target.Interior.ColorIndex = 34

    Case "SL", "FS"
    Target.Interior.ColorIndex = 36

    Case Is = "ML"
    Target.Interior.ColorIndex = 24

    Case Else
    If Left(Target.Value)="V" Then
    Target.Interior.ColorIndex = 35
    Else
    'No conditions met, so make it normal
    Target.Interior.ColorIndex = -4142
    End if
    End Select[/vba] [/left]
    Ken Puls, CMA - Microsoft MVP (Excel)
    I hate it when my computer does what I tell it to, and not what I want it to.

    Learn how to use our KB tags! -||- Ken's Excel Website -||- Ken's Excel Forums -||- My Blog -||- Excel Training Calendar

    This is a shameless plug for my new book "RibbonX - Customizing the Office 2007 Ribbon". Find out more about it here!

    Help keep VBAX clean! Use the 'Thread Tools' menu to mark your own threads solved!




    Reply With Quote Reply With Quote
  8. 01-21-2006, 04:05 PM #8
    geekgirlau
    geekgirlau is offline
    Moderator VBAX Master geekgirlau's Avatar
    Joined
    Aug 2004
    Location
    Melbourne, Australia
    Posts
    1,464
    Location
    I thought of that Ken, but of course if you type "PLACE" or "VICTORY" in a cell they will also be coloured ...
    Reply With Quote Reply With Quote
  9. 01-23-2006, 03:53 PM #9
    Ken Puls
    Ken Puls is offline
    Moderator VBAX Guru Ken Puls's Avatar
    Joined
    Aug 2004
    Location
    Nanaimo, BC, Canada
    Posts
    4,001
    Location
    Good point, Anne!
    Ken Puls, CMA - Microsoft MVP (Excel)
    I hate it when my computer does what I tell it to, and not what I want it to.

    Learn how to use our KB tags! -||- Ken's Excel Website -||- Ken's Excel Forums -||- My Blog -||- Excel Training Calendar

    This is a shameless plug for my new book "RibbonX - Customizing the Office 2007 Ribbon". Find out more about it here!

    Help keep VBAX clean! Use the 'Thread Tools' menu to mark your own threads solved!




    Reply With Quote Reply With Quote
« Previous Thread | Next Thread »

Posting Permissions

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

Forum Rules