Conditional Formatting

[wadasczi]

I have a spredsheet set up with conditional formatting as it relates to
calendar dates. My question to the world is can I add something to the
formula that will "add some text" when the condition is reached. Right
now the cells just change color.

V/R

Walt

------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

[Lee]

Add a separate IF statement for those cells where a
condition is to be evaluated.

>.
>

[diego]

I am using a conditional formatting to create a group by
using different colour, however, it seems that Excel only
allows up to 3 conditions, is there a way to get around
this problem without using VBA code since the code needs
to be run everytime a value changes ?

Thanks

[Dave Peterson]

You can have an macro run without (almost) without your knowledge. You wouldn't
have to start it yourself.

You could either use a worksheet_change event or a worksheet_calculate event
depending on how the data changes.

Here's a link to a recent thread:
http://google.com/groups?threadm=452c01c375fc%245e8c0970%24a601280a%40phx.gbl

--

Dave Peterson
ec3...@msn.com

[jr]

I don't know what your conditions are but, while you're
limited by 3 conditions, those conditions can be complex.
You have to be creative - even artistic but the goal is
to pack multiple conditions into a single key.
I know this is a non-answer but, if you provide a
scenario, perhaps we could suggest something specific.

>.
>

[baz]

Is the following possible?

Conditional Formatting limits you to only three conditions. Is there a
way to get more conditions or do the following.

I am trying to create several, say 10 conditions depending upon
"severity" of the result.

Value 10+ would have light yellow background and Bold black type
Value 6 thru 9.99 would have light green background and Bold black
type
Value 2 thru 5.99 would have light pink background and Bold black type
Value -2 thru 2 would be plain
Value -2.1 thru -5.99 would have light pink background and Bold RED
type
Value -6 thru -9.99 would have light green background and Bold RED
type
Valur -10 and lower would have light yellow background and Bold RED
type

I hope thisis clear.

Thanks in advance for any help.

baz

Please post answer or respond to zgeekz at yahoo dot com as response
email address is bogus to prevent spa

[Dave Peterson]

Not using conditional formatting. That's limited to 3 formats (plus the
"normal").

If you really need this, you can use an event macro. And that would depend on
how those cells are updated--formula or typing.

If you were just typing values into the worksheet, then you could use something
like:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Range("a1:a9")) Is Nothing Then Exit Sub

With Target
If IsNumeric(.Value) = False Then
.Font.ColorIndex = xlAutomatic
.Interior.ColorIndex = xlNone
.Font.Bold = False
Else
.Font.Bold = True
Select Case .Value
Case Is <= -10
.Font.ColorIndex = 1
.Interior.ColorIndex = 2
Case Is <= -6
.Font.ColorIndex = 3
.Interior.ColorIndex = 4
Case Is < -2
.Font.ColorIndex = 5
.Interior.ColorIndex = 6
Case Is <= 2
.Font.ColorIndex = xlAutomatic
.Interior.ColorIndex = xlNone
.Font.Bold = False
Case Is < 6
.Font.ColorIndex = 7
.Interior.ColorIndex = 8
Case Is < 10
.Font.ColorIndex = 9
.Interior.ColorIndex = 10
Case Else
.Font.ColorIndex = 11
.Interior.ColorIndex = 12
End Select
End If
End With

End Sub

But I was too lazy to translate your colors to the real numbers. Record a macro
when you change the colors the way you want them. Look at the macro and pick
out those numbers for the interior and font.

Also, be careful. Check to see if those case's are ok. (It starts at the
smallest and goes up. You may want to change "Case is <" "case is <=" (added
an equal sign).)

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

The simple instructions: right click on the worksheet tab that should get this
coloring. Select View code and paste this into the right hand side.

And one more thing. I used A1:A9 as the range to get shaded. Change this to
what you want.

--

Dave Peterson
ec3...@msn.com