What formula do I need?

[Lydia]

I want a symbol or color to appear when I have the following words entered
into a cell (failed, pass, pending and offer made). I'm thinking I need an IF
formula or conditional formatting, but I've never used either. I would rate
myself as a 5 on Excel on a 1-10 scale. I'm no expert!

Thanks for all the help!

 

[T. Valko]

Try this to color the cell...

Select the cell of interest. Let's assume this is cell A1.

Goto the menu Format>Conditional Formatting
Select the Formula Is option
Enter this formula in the box on the right:
=OR(A1="failed",A1="pass",A1="pending",A1="offer made")
Click the Format button
Select the desired style(s)
OK out

 

[Jacob Skaria]

1. Select the cell/Range (say A1:A10). Please note that the cell reference A1
mentioned in the formula is the active cell in the selection. Active cell
will have a white background even after selection

2. From menu Format>Conditional Formatting>
3. For Condition1>Select 'Formula Is' and enter the below formula

=OR(A1="failed",A1="pass",A1="pending",A1="offer made")

4. Click Format Button>Pattern and select your color (say Red)
5. Hit OK


If this post helps click Yes

 

[Roger Govier]

Hi Lydia

I am assuming you want a different colour for each condition.
Conditional Formatting only allows three colours, but if you count the norm
of black on white as one, then it could be 4.

However, this small macro will colour the cells as per the contents, and can
be easily extended for more cases.
It first sets al of the cells in the range selected back to No Fill, then
colours the cell background according to the text found in the cell.
You can change the colour index to any value you want.

Sub FillColours()

Dim rng As Range, result As String
Selection.Interior.ColorIndex = xlNone
For Each rng In Selection
result = rng.Value
'failed, pass, pending and offer made
Select Case result
Case "failed"
rng.Interior.ColorIndex = 3 ' Red
Case "pass"
rng.Interior.ColorIndex = 6 ' Yellow
Case "pending"
rng.Interior.ColorIndex = 7 ' Pink
Case "offer made"
rng.Interior.ColorIndex = 4 ' Green
Case Else
End Select
Next
End Sub

To Install
Copy the code as above
Press Alt+F11 to invoke the VB Editor
Insert>Module ( or Alt+I+M )
Paste the code into the white pane that appears.
Alt+F11 to return to Excel

To Use
Highlight the range of cells you want to colour
Alt+F8 ( or Tools>Macros)
Select the macro FillColours
Run

--
Regards
Roger Govier

I want a symbol or color to appear when I have the following words entered
into a cell (failed, pass, pending and offer made). I'm thinking I need an
IF
formula or conditional formatting, but I've never used either. I would
rate
myself as a 5 on Excel on a 1-10 scale. I'm no expert!

Thanks for all the help!
--
Lydia

__________ Information from ESET Smart Security, version of virus
signature database 4530 (20091021) __________

The message was checked by ESET Smart Security.

http://www.eset.com

__________ Information from ESET Smart Security, version of virus signature database 4530 (20091021) __________

The message was checked by ESET Smart Security.

http://www.eset.com

 

[Lydia]

I must be REALLY stupid. Because I don't see any of those options. Did I
mentioned I'm using 2007?

 

[T. Valko]

Did I mentioned I'm using 2007?

Nope! And that makes a BIG difference!

After reading Roger's reply I think he may have interpreted your intentions
correctly.

So, do you wnat a different color for each of the different words failed,
pass, pending and offer made or do you want one color for any of those
words?

 

[Lydia]

I tried, but VB didn't like xlNone. Now what?

 

[Jacob Skaria]

For 2007
Goto Home tab>Styles>Conditional Formatting>Manage rules>New rule>Use a
formula to determine which cells to format
Enter this formula in the box below:
Click the Format button and Select the desired style(s)..OK

If this post helps click Yes

 

[Lydia]

This kind of helped. I need a different color or symbol or each (failed,
pass, pending and offer made). It's in an entrire row, not just a cell.

Thanks for helping.

 

[Lydia]

YES
--
Lydia

Nope! And that makes a BIG difference!

After reading Roger's reply I think he may have interpreted your intentions
correctly.

So, do you wnat a different color for each of the different words failed,
pass, pending and offer made or do you want one color for any of those
words?

--
Biff
Microsoft Excel MVP





.

 

[T. Valko]

Yes to which?

1. You want a different color for each of the different words failed, pass,
pending and offer made.

2. You want one color for any of those words?

 

[Roger Govier]

Hi Lydia

For XL2007, change the line to read

Selection.Interior.Pattern = xlNone


--
Regards
Roger Govier

I tried, but VB didn't like xlNone. Now what?
--
Lydia




__________ Information from ESET Smart Security, version of virus
signature database 4530 (20091021) __________

The message was checked by ESET Smart Security.

http://www.eset.com

__________ Information from ESET Smart Security, version of virus signature database 4530 (20091021) __________

The message was checked by ESET Smart Security.

http://www.eset.com

 

[Lydia]

Sorry. Yes, to wanting a different color for each.

Thanks for your help.

 

[T. Valko]

Ok, let's assume you want to color format A1:C1 when cell A1 contains one of
these words: failed, pass, pending, offer made.

Select thr range A1:C1 starting from cell A1.

Goto Home tab>Styles>Conditional Formatting>Manage rules>New rule>Use a
formula to determine which cells to format
Enter this formula in the box below:
=$A1="failed"
Click the Format button
Select the desired style(s)
OK>OK>New Rule>Use a formula to determine which cells to format
Enter this formula in the box below:
=$A1="pass"
Click the Format button
Select the desired style(s)
OK>OK>New Rule>Use a formula to determine which cells to format
Enter this formula in the box below:
=$A1="pending"
Click the Format button
Select the desired style(s)
OK>OK>New Rule>Use a formula to determine which cells to format
Enter this formula in the box below:
=$A1="offer made"
Click the Format button
Select the desired style(s)
OK out