Thanks for the reply.

I've tried the temporary variable thing, but it doesn't seem to b
working for me. It seems like the current font color and font bol
state aren't being stored into the variables correctly. I'm not sur
why. Here's the code (FontColor and FontBold are the temporar
variables I'm using):

' Toggles the background color
' Keyboard Shortcut: Ctrl+Shift+O
Sub BackgroundToggle()
Dim FontColor, FontBold
FontColor = Selection.Font.Color()
FontBold = Selection.Font.Bold()
If Selection.Interior.ColorIndex = xlNone Then
Selection.Interior.ColorIndex = 2
ElseIf Selection.Interior.ColorIndex = 2 Then
Selection.Interior.ColorIndex = 1
Selection.Font.Color = RGB(255, 255, 255)
Selection.Font.Bold = True
ElseIf Selection.Interior.ColorIndex = 1 Then
Selection.Interior.ColorIndex = 48
Selection.Font.Color = RGB(255, 255, 255)
Selection.Font.Bold = True
ElseIf Selection.Interior.ColorIndex = 48 Then
Selection.Interior.ColorIndex = 35
Selection.Font.ColorIndex = 1
Selection.Font.Bold = True
Else
Selection.Interior.ColorIndex = xlNone
Selection.Font.Bold = FontColor
Selection.Font.Color = FontColor
End If
End Sub

Any suggestions

--
GK8053
-----------------------------------------------------------------------
GK80535's Profile: http://www.excelforum.com/member.php...fo&userid=1546
View this thread: http://www.excelforum.com/showthread.php?threadid=27213

There are a few things to address...

Font.Bold returns a boolean; Font.Color returns a long.
Try dim each that way instead of as a variant. Variants are a waste of memory.

In your code below, both variables are not needed as they are not reseting
anything. The fourth line from the bottom also applies the wrong variable to
Font.Bold.

In the last ElseIf construct, did you mean:
Selection.Font.Color = xlColorIndexAutomatic
Selection.Font.Bold = False

When evaluating the color index for a "selection", a mixed bag (meaning not
all the cells in your selection have the same color index) returns nothing.
Try evaluating a single cell in the selection, then change the entire
selection accordingly.
If Selection.Cells(1, 1).Interior.ColorIndex = xlNone Then
'do the color thing
End If

Hope this helps.

Dale Preuss


"GK80535" wrote:


Thanks for the reply.

I've tried the temporary variable thing, but it doesn't seem to be
working for me. It seems like the current font color and font bold
state aren't being stored into the variables correctly. I'm not sure
why. Here's the code (FontColor and FontBold are the temporary
variables I'm using):

' Toggles the background color
' Keyboard Shortcut: Ctrl+Shift+O
Sub BackgroundToggle()
Dim FontColor, FontBold
FontColor = Selection.Font.Color()
FontBold = Selection.Font.Bold()
If Selection.Interior.ColorIndex = xlNone Then
Selection.Interior.ColorIndex = 2
ElseIf Selection.Interior.ColorIndex = 2 Then
Selection.Interior.ColorIndex = 1
Selection.Font.Color = RGB(255, 255, 255)
Selection.Font.Bold = True
ElseIf Selection.Interior.ColorIndex = 1 Then
Selection.Interior.ColorIndex = 48
Selection.Font.Color = RGB(255, 255, 255)
Selection.Font.Bold = True
ElseIf Selection.Interior.ColorIndex = 48 Then
Selection.Interior.ColorIndex = 35
Selection.Font.ColorIndex = 1
Selection.Font.Bold = True
Else
Selection.Interior.ColorIndex = xlNone
Selection.Font.Bold = FontColor
Selection.Font.Color = FontColor
End If
End Sub

Any suggestions?


--
GK80535
------------------------------------------------------------------------
GK80535's Profile: http://www.excelforum.com/member.php...o&userid=15461
View this thread: http://www.excelforum.com/showthread...hreadid=272134

On Mon, 25 Oct 2004 13:33:43 -0500, GK80535
wrote:

The Color and Bold properties aren't arrays. You shouldn't have the empty
parentheses after these properties.

However, as long as you are not changing them, there's no reason to read,
save, and set them to the values they already have. You didn't set the font
when the interior was xlNone. You don't need to do it in the Else case,
either. In short, I don't think you need these variables at all.

It looks to me as though you cycle through these 5 colors. If so, two points:

1. You set the font to Bold and you never change that. If for some reason, it
isn't bold at the start, I would set that once, at the top.

2. Only set the font color when it changes (when interior changes from 2 to 1
or from 48 to 35)

I don't like to do all that typing, so I would write it with a With/End With
block and a Select Case block, like this

Sub BackgroundToggle()
With Selection
.Font.Bold = True
Select Case .Interior.ColorIndex
Case xlNone
.Interior.ColorIndex = 2
Case 2
.Interior.ColorIndex = 1
.Font.Color = RGB(255, 255, 255) 'white
Case 1
.Interior.ColorIndex = 48
Case 48
.Interior.ColorIndex = 35
.Font.ColorIndex = 1 'black?
Case Else '35 ???
.Interior.ColorIndex = xlNone
End If
End With
End Sub


Thanks for the reply.

I've tried the temporary variable thing, but it doesn't seem to be
working for me. It seems like the current font color and font bold
state aren't being stored into the variables correctly. I'm not sure
why. Here's the code (FontColor and FontBold are the temporary
variables I'm using):

' Toggles the background color
' Keyboard Shortcut: Ctrl+Shift+O
Sub BackgroundToggle()
Dim FontColor, FontBold
FontColor = Selection.Font.Color()
FontBold = Selection.Font.Bold()
If Selection.Interior.ColorIndex = xlNone Then
Selection.Interior.ColorIndex = 2
ElseIf Selection.Interior.ColorIndex = 2 Then
Selection.Interior.ColorIndex = 1
Selection.Font.Color = RGB(255, 255, 255)
Selection.Font.Bold = True
ElseIf Selection.Interior.ColorIndex = 1 Then
Selection.Interior.ColorIndex = 48
Selection.Font.Color = RGB(255, 255, 255)
Selection.Font.Bold = True
ElseIf Selection.Interior.ColorIndex = 48 Then
Selection.Interior.ColorIndex = 35
Selection.Font.ColorIndex = 1
Selection.Font.Bold = True
Else
Selection.Interior.ColorIndex = xlNone
Selection.Font.Bold = FontColor
Selection.Font.Color = FontColor
End If
End Sub

Any suggestions?