Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formatting multiple cells if one cells says "X"...
Hi All
I know that I need to write code (VBA) to address this conditional formatting, but I'm a novice and after reading all the postings on conditional formatting I can't work it out - please help... There are 5 conditions and the formatting applies to a growing number of cells (current range is M14:GR603 - but the number of rows will increase). The conditions a If "Retail" is entered in column E of this row, then colour this cell pink If "Brand" is entered in column E of this row, then colour this cell blue If "Special" is entered in column E of this row, then colour this cell yellow If "Generic" is entered in column E of this row, then colour this cell green If "Other" is entered in column E of this row, then don't colour the cell. I am using Excel 2000 (if that makes any difference). Thank for your help BeSmart |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formatting multiple cells if one cells says "X"...
BeSmart,
You can use the worksheet's change event keyed to column E. Copy the code below, right click on the sheet tab, select "View Code" and paste the code in the window that appears. HTH, Bernie MS Excel MVP Private Sub Worksheet_Change(ByVal Target As Range) Dim myCell As Range If Not Intersect(Target, Range("E:E")) Is Nothing Then For Each myCell In Intersect(Target, Range("E:E")) If myCell.Value = "Retail" Then Range(Range("M" & myCell.Row), Range("AG" & myCell.Row)) _ .Interior.ColorIndex = 38 End If If myCell.Value = "Brand" Then Range(Range("M" & myCell.Row), Range("AG" & myCell.Row)) _ .Interior.ColorIndex = 37 End If If myCell.Value = "Special" Then Range(Range("M" & myCell.Row), Range("AG" & myCell.Row)) _ .Interior.ColorIndex = 36 End If If myCell.Value = "Generic" Then Range(Range("M" & myCell.Row), Range("AG" & myCell.Row)) _ .Interior.ColorIndex = 35 End If If myCell.Value = "Other" Then Range(Range("M" & myCell.Row), Range("AG" & myCell.Row)) _ .Interior.ColorIndex = xlNone End If Next myCell End If End Sub "BeSmart" wrote in message ... Hi All I know that I need to write code (VBA) to address this conditional formatting, but I'm a novice and after reading all the postings on conditional formatting I can't work it out - please help... There are 5 conditions and the formatting applies to a growing number of cells (current range is M14:GR603 - but the number of rows will increase). The conditions a If "Retail" is entered in column E of this row, then colour this cell pink If "Brand" is entered in column E of this row, then colour this cell blue If "Special" is entered in column E of this row, then colour this cell yellow If "Generic" is entered in column E of this row, then colour this cell green If "Other" is entered in column E of this row, then don't colour the cell. I am using Excel 2000 (if that makes any difference). Thank for your help BeSmart |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Formatting - find cells containg "+" | Excel Discussion (Misc queries) | |||
Combine text from multiple cells into one cell - =(A1&","&A2","&A3 | Excel Worksheet Functions | |||
Conditional formatting cells with formulas = "#VALUE!" | Excel Discussion (Misc queries) | |||
Using conditional formatting to "blank" cells | Excel Discussion (Misc queries) | |||
Can you "duplicate" "copy" listboxes and code to multiple cells? | Excel Programming |