create a formula to produce a color in excel

  • Thread starter Thread starter Senie
  • Start date Start date
S

Senie

I am trying to produce a formula as to where I key in a specific letter and
the cell will turn to a specific color. for example: if I type in the Letter
"A" the cell the letter is typed in will turn to the color Light Blue.
 
If not more than 3 colours, try conditional formatting

Example:
Select col A, click Format > Conditional Formatting
Apply the following using "Formula Is:"
Cond1: =A1="A"
Format to taste

Cond2: =A1="B"
Format to taste

Cond3: =A1="C"
Format to taste
Ok out

Test it out by inputting the 3 letters* (A, B, C) in any cell in col A, the
corresponding format will be triggered.
*non case sensitive
 
It worked when I selected one condition but when I went to the second
condition it did not worked and I tried mulitples ways. However, I did learn
something new.
 
Sure go ahead and give me the information to use sheet event code which you
can supply. It will be a learning adventure for me.

I am also going to check out the website you have listed as well.
Thank you so much!
 
I like your attitude about the learning adventure. Keep it up.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Num As Long
Dim rng As Range
Dim vRngInput As Variant
Set vRngInput = Intersect(Target, Range("D:D"))
If vRngInput Is Nothing Then Exit Sub
On Error GoTo endit
Application.EnableEvents = False
For Each rng In vRngInput
'Determine the color
Select Case UCase(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
End Select
'Apply the color
rng.Interior.ColorIndex = Num
Next rng
endit:
Application.EnableEvents = True
End Sub

Right-click on your sheet tab and "View Code".

Copy/paste the code into that module.

Adjust Range("D:D") to suit


Gord
 
It should work fine, Senie

Try this link to a quick sample with all 3 conditions implemented
http://www.freefilehosting.net/download/NDIzNDM=
senie_CF.xls
(contains a screenshot of the CF dialog)

Copy n paste the entire link into your browser address (including the "=" at
the end). Do not click direct on the link if reading this from MS' webpage.
 
Hey, this is pretty neat, I'm intrigued and learning a lot here. Now
that I know about changing the cell color, is there a way to change
the font color as opposed to (or in addition to) the cell color?

Thanks,

--Jim
 
The colors worked fine on a new worksheet. However, on the worksheet I need
this code on is not working. However, I do have formulas applied to the cells
that the code would have to be used in. Is it possible that that could be the
problem? I have been working at this since 8:30am Eastern time. HELP PlZ.
 
The code given was worksheet_change event code.

A change due to a formula will not trigger the code, only hard typing or a cell
edit will trigger.

If your formulas return a letter you should be able to make this work

Tested with formula in A1 =D1 and copied down.

Enter A through F in column D and colors follow in A1:A100

Private Sub Worksheet_Calculate()
Dim Target As Range
For Each Target In Me.Range("A1:A100")
With Target
Select Case UCase(Target.Value)
Case Is = "A": .Interior.ColorIndex = 7
Case Is = "B": .Interior.ColorIndex = 10
Case Is = "C": .Interior.ColorIndex = 16
Case Is = "D": .Interior.ColorIndex = 4
Case Is = "E": .Interior.ColorIndex = 6
Case Is = "F": .Interior.ColorIndex = 3
'etc.
End Select
End With
Next Target
End Sub


Gord
 
Hi Gordon,

I'm following along pretty well, am able to get the font and the
cell color to change. I'm new to macros, and don't know how to make it
so that both the font color and the cell color change, like white
font, red background, or green font with yellow background ... can it
be done?

Thanks,

--Jim
 
You would need some changes to existing code.

Try this..................

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Num As Long
Dim Num2 As Long
Dim rng As Range
Dim vRngInput As Range
Set vRngInput = Intersect(Target, Range("D:D"))
If vRngInput Is Nothing Then Exit Sub
On Error GoTo endit
Application.EnableEvents = False
For Each rng In vRngInput
'Determine the color
Select Case UCase(rng.Value)
Case Is = "A": Num = 10: Num2 = 2 'green and white
Case Is = "B": Num = 1: Num2 = 6 'black and yellow
Case Is = "C": Num = 5: Num2 = 2 'blue and white
Case Is = "D": Num = 7: Num2 = 1 'magenta and black
Case Is = "E": Num = 45: Num2 = 10 'orange and green
Case Is = "F": Num = 3: Num2 = 34 'red and light turquoise
End Select
'Apply the color
rng.Interior.ColorIndex = Num
rng.Font.ColorIndex = Num2
Next rng
endit:
Application.EnableEvents = True
End Sub

For a list of colorindex numbers see David McRitchie's site

http://www.mvps.org/dmcritchie/excel/colors.htm


Gord
 
You would need some changes to existing code.

Try this..................

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Num As Long
Dim Num2 As Long
Dim rng As Range
Dim vRngInput As Range
Set vRngInput = Intersect(Target, Range("D:D"))
If vRngInput Is Nothing Then Exit Sub
On Error GoTo endit
Application.EnableEvents = False
For Each rng In vRngInput
'Determine the color
Select Case UCase(rng.Value)
Case Is = "A": Num = 10: Num2 = 2 'green and white
Case Is = "B": Num = 1: Num2 = 6 'black and yellow
Case Is = "C": Num = 5: Num2 = 2 'blue and white
Case Is = "D": Num = 7: Num2 = 1 'magenta and black
Case Is = "E": Num = 45: Num2 = 10 'orange and green
Case Is = "F": Num = 3: Num2 = 34 'red and light turquoise
End Select
'Apply the color
rng.Interior.ColorIndex = Num
rng.Font.ColorIndex = Num2
Next rng
endit:
Application.EnableEvents = True
End Sub

For a list of colorindex numbers see David McRitchie's site

http://www.mvps.org/dmcritchie/excel/colors.htm

Gord




- Show quoted text -
Click to expand...

Most Excellent! That's what I meant, and it works perfectly.

Thanks for your time and skill with macros!

--Jim
 
Back
Top