Assigning a cell colors via IF-THEN statements

[Gravy Man]

Hello!

Can you assign a cell a specific color using an If-Then statement...instead
of using conditional formatting?

For example, if I'm evaluating a value in cell C8, which will assign a color
to cell B8 accordingly. Is this possible?

Thank you for the help!

 

[FSt1]

hi
no
formulas return values, they cannot perform actions such as assign formats.
this is one of the reasons that conditional formating was introduced. i would
concentrate on the conditional format.

Regards
FSt1

 

[Dave]

Hi,
If you've used up your 3 conditional formats, you'll have to revert to macros.
Having said that, if you just want to change the font colour only, you can
use a custom format.
The following is a paste from somewhere, but I can't remember where.
Apologies to the original author.

Value Font Color
===== ==========
< -100 Red
< -20 Blue
< 0 Yellow
< 20 Green
< 100 Maroon

= 100 Purple

The last three we'll leave for conditional formatting. The first three,
however, will be done using this format, which we enter by choosing
Format/Cells/Number/Custom:

[Red][<-100]General;[Blue][<-20]General;[Yellow]General;@

Of course, we don't have to use General - we could use any other numeric
format. The available color names are [BLACK], [BLUE], [CYAN], [GREEN],
[MAGENTA], [RED], [WHITE], and [YELLOW], or you can use any of the 56 colors
in the XL color palette by using [Color1] through [Color56]. Thanks, MVP Bob
Umlas for telling me about the latter method!

Regards - Dave.

 

[ShaneDevenshire]

Hi,

You could use VBA as follows:

Sub ColorCells()
Dim cell As Excel.Range
For Each cell In Selection
With cell.Interior
Select Case cell
Case Is < -10
.ColorIndex = 14
Case 0
.ColorIndex = 41
Case 1, 4, 6
.ColorIndex = 13
Case 7 To 20
.ColorIndex = 8
Case Else
.ColorIndex = 45
End Select
End With
Next cell
End Sub

This example shows how to use many of the Select Case options.
--
Cheers,
Shane Devenshire
Microsoft Excel MVP
Join http://setiathome.berkeley.edu/ and download a free screensaver to help
find life off planet earth.