Excel 2007 Custom Formatting using [ColorXX]

  • Thread starter Thread starter GollyJer
  • Start date Start date
G

GollyJer

I like to use custom cell formatting for simple cell coloring of positive
and negative numbers (leaving conditional formatting for more intensive
tasks). In Excel 2003 you could use [ColorXX], where XX is a number
corresponding to a color in the color palette of the active workbook.
Determining what number resulted in a specific color was straight forward as
it matched the layout of the color palette. In Excel 2007 I can't seem to
determine how the number used corresponds to a color palette somewhere.

An example of a format string I'm using is:
_(* #,##0.00_);[Color25]_(* (#,##0.00);_(* "-"??_);_(@_)
where color 25 is a blue color. Any negative numbers will be Color25.
Changing to Color24 and Color26 doesn't reveal a corresponding color pattern
that I recognize which makes it difficult if I want to change to <desired
color>. I'm sure it's not random. Anyone have figured it out for 2007?

Thanks,
Jeremy
 
With Excel 2007, there are many palettes. I think you could use another
theme such as "verve" to obtain a similar color. Also, you could custom
your current style.
HTH
Daniel
 
I know about the palettes and themes.
My question is how do you determine what [Color25], from the example posted,
will return from whatever palette/theme is selected.

Daniel.C said:
With Excel 2007, there are many palettes. I think you could use another
theme such as "verve" to obtain a similar color. Also, you could custom
your current style.
HTH
Daniel
I like to use custom cell formatting for simple cell coloring of positive
and negative numbers (leaving conditional formatting for more intensive
tasks). In Excel 2003 you could use [ColorXX], where XX is a number
corresponding to a color in the color palette of the active workbook.
Determining what number resulted in a specific color was straight forward
as it matched the layout of the color palette. In Excel 2007 I can't
seem to determine how the number used corresponds to a color palette
somewhere.

An example of a format string I'm using is:
_(* #,##0.00_);[Color25]_(* (#,##0.00);_(* "-"??_);_(@_)
where color 25 is a blue color. Any negative numbers will be Color25.
Changing to Color24 and Color26 doesn't reveal a corresponding color
pattern that I recognize which makes it difficult if I want to change to
<desired color>. I'm sure it's not random. Anyone have figured it out
for 2007?

Thanks,
Jeremy
Click to expand...
Click to expand...
 
You could list the colors in a sheet:

Sub ListColors()
Dim Counter As Integer
Dim ListRow As Integer
Dim ListCol As Integer
ListCol = 1
For Counter = 1 To 56
ListRow = ListRow + 1
Cells(ListRow, ListCol).Value = Counter
Cells(ListRow, ListCol + 1).Interior.ColorIndex = Counter
If ListRow = 10 Then
ListRow = 0
ListCol = ListCol + 2
End If
Next
End Sub

--
Jim
|I know about the palettes and themes.
| My question is how do you determine what [Color25], from the example
posted,
| will return from whatever palette/theme is selected.
|
| | > With Excel 2007, there are many palettes. I think you could use another
| > theme such as "verve" to obtain a similar color. Also, you could custom
| > your current style.
| > HTH
| > Daniel
| >
| >> I like to use custom cell formatting for simple cell coloring of
positive
| >> and negative numbers (leaving conditional formatting for more intensive
| >> tasks). In Excel 2003 you could use [ColorXX], where XX is a number
| >> corresponding to a color in the color palette of the active workbook.
| >> Determining what number resulted in a specific color was straight
forward
| >> as it matched the layout of the color palette. In Excel 2007 I can't
| >> seem to determine how the number used corresponds to a color palette
| >> somewhere.
| >>
| >> An example of a format string I'm using is:
| >> _(* #,##0.00_);[Color25]_(* (#,##0.00);_(* "-"??_);_(@_)
| >> where color 25 is a blue color. Any negative numbers will be Color25.
| >> Changing to Color24 and Color26 doesn't reveal a corresponding color
| >> pattern that I recognize which makes it difficult if I want to change
to
| >> <desired color>. I'm sure it's not random. Anyone have figured it out
| >> for 2007?
| >>
| >> Thanks,
| >> Jeremy
| >
| >
 
Turns out they're the same as Excel 2003 and earlier versions of Excel.
Thanks Jim.
 
Back
Top