Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Designating color in one cell based on data entered in another cel
I need a way (formula, format, etc) for the background color in one cell to
be determined by the data entered in another cell. For example: Cell C6 will have a number manually entered. Cell D6 will have a single letter manually entered into it. Based on the letter entered in D6, I want the background color in cell C6 to be a certain color. If the letter in D6 is P then the background in C6 needs to be green. If the letter in D6 is I, then the background needs to be red. I have 7 letters that will be entered into cell D6 that will need to determine 7 different background colors for cell C6. How can I make this happen using excel 2003? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Designating color in one cell based on data entered in another cel
Suggest a SELECTCASE macro within a WORKSHEET_CHANGE event macro
-- Don Guillett Microsoft MVP Excel SalesAid Software "Diogie" wrote in message ... I need a way (formula, format, etc) for the background color in one cell to be determined by the data entered in another cell. For example: Cell C6 will have a number manually entered. Cell D6 will have a single letter manually entered into it. Based on the letter entered in D6, I want the background color in cell C6 to be a certain color. If the letter in D6 is P then the background in C6 needs to be green. If the letter in D6 is I, then the background needs to be red. I have 7 letters that will be entered into cell D6 that will need to determine 7 different background colors for cell C6. How can I make this happen using excel 2003? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Designating color in one cell based on data entered in another cel
You need VBA code or Excel 2007.
"Diogie" wrote: I need a way (formula, format, etc) for the background color in one cell to be determined by the data entered in another cell. For example: Cell C6 will have a number manually entered. Cell D6 will have a single letter manually entered into it. Based on the letter entered in D6, I want the background color in cell C6 to be a certain color. If the letter in D6 is P then the background in C6 needs to be green. If the letter in D6 is I, then the background needs to be red. I have 7 letters that will be entered into cell D6 that will need to determine 7 different background colors for cell C6. How can I make this happen using excel 2003? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Designating color in one cell based on data entered in another cel
Option Compare Text
Private Sub Worksheet_Change(ByVal Target As Range) Dim Num As Long Dim rng As Range Dim vRngInput As Variant Set vRngInput = Me.Range("D6") If vRngInput Is Nothing Then Exit Sub On Error GoTo endit Application.EnableEvents = False For Each rng In vRngInput 'Determine the color Select Case rng.Value Case Is = "A": Num = 10 'green Case Is = "B": Num = 1 'black Case Is = "C": Num = 5 'blue Case Is = "D": Num = 7 'magenta Case Is = "E": Num = 46 'orange Case Is = "F": Num = 3 'red Case Is = "G": Num = 6 'yellow End Select 'Apply the color rng.Offset(0, -1).Interior.ColorIndex = Num Next rng endit: Application.EnableEvents = True End Sub This is sheet event code. Right-click on the sheet tab and "View Code". Copy/paste the code into that sheet module. Edit letters and color numbers to suit. Gord Dibben MS Excel MVP On Fri, 7 Sep 2007 07:30:02 -0700, Diogie wrote: I need a way (formula, format, etc) for the background color in one cell to be determined by the data entered in another cell. For example: Cell C6 will have a number manually entered. Cell D6 will have a single letter manually entered into it. Based on the letter entered in D6, I want the background color in cell C6 to be a certain color. If the letter in D6 is P then the background in C6 needs to be green. If the letter in D6 is I, then the background needs to be red. I have 7 letters that will be entered into cell D6 that will need to determine 7 different background colors for cell C6. How can I make this happen using excel 2003? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Select correct worksheet based on data entered into a cell | Excel Worksheet Functions | |||
How to sum data based on font or cell color? | Excel Worksheet Functions | |||
How do I change cell color based upon data range within the cell? | Excel Worksheet Functions | |||
Changing chart color based on number entered | Charts and Charting in Excel | |||
How do I change fill color in excel based on data in the cell | Excel Worksheet Functions |