Fill Cells

MMCC67

New Member
Joined
Jul 4, 2008
Messages
8
Hi All,

First time poster here, but I think it'll not be my last!!

How do you change the fill colour of a cell depending on the cells entry?

For example, if the cell is a pass I want to type the letter P and the cell to be highlighted in green and if it's a fail I want to type the letter f and want the cell to be highlighted in red.

I tried to do this using an IF formula, but this creates a circular reference. Is this the way it has to be done, or is there an easier alternative?

I've always thought I was half decent with Excel - obviously not!!

Any help would be appreciated.
 

Some videos you may like

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

cummingsea

Active Member
Joined
Aug 9, 2005
Messages
339
Office Version
  1. 2019
Platform
  1. Windows
you can do this using Conditional Formatting
Select Conditional Format, select new rule,select cell only cell that contain in the first box select TEXT, 2nd box containing, 3rd box type the letter you want then click on format button ans set color click ok
If you have Excel 2007 you can set up a color for every letter you want, but if you have 2002 or 2003 you are limited to 3 colors or you will have to use VBA for more then 3
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
One way is conditional formatting - see http://www.contextures.com/xlCondFormat01.html

Another is to use an event procedure - right click the sheet tab, select View Code and paste in

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Select Case UCase(Target.Value)
    Case "P"
        Target.Interior.ColorIndex = 4
    Case "F"
        Target.Interior.ColorIndex = 3
    Case Else
        Target.Interior.ColorIndex = xlNone
End Select
End Sub

then close the code window.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,469
Messages
5,596,310
Members
414,052
Latest member
Dual Showman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top