I'm trying to write a Macro that toggles through background color an
font styles.
No one's been able to help me out from my previous posts, so I'm stil
searching.

I need a function that returns the current selection's font style (red
italic, bold, times roman, etc.) so that I can store it in a variabl
before my toggle begins and so that I can restore the style at the en
of my toggle. Can anyone please help me out with this?

Here's my code:

' Toggles the background color
' Keyboard Shortcut: Ctrl+Shift+O
Sub BackgroundToggle()
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 = False
Selection.Font.ColorIndex = 1
End If
End Su

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

Excel has a cycle font function, Customize toolbars, Format and look for the
palette icon.

Following should cycle font colour after first setting to bold, and a very
crude way to restore original. Not sure what you want to do with font styles
but maybe you can incorporate. Not thoroughly tested.

Sub Cyclefont()
Dim rSpare
Set rSpare = [a1].SpecialCells(xlLastCell).Offset(1, 0)
Selection(1).Copy rSpa rSpare.ClearContents
'not pastespecial to avoid moving selection - lazy!
again:
With Selection(1).Font
cx = .ColorIndex
.Bold = True
Select Case cx
Case xlAutomatic: cx = 1
Case 16: cx = 33 'avoid chart colours
Case 56: cx = xlAutomatic
Case Else: cx = cx + 1 End Select
.ColorIndex = cx
End With
Reply = MsgBox("Yes: accept" & vbCr & _
"No: Next" & vbCr & _
"Cancel: restore original", vbYesNoCancel, _
"Color index " & cx)
If Reply = vbNo Then GoTo again
If Reply = vbCancel Then
rSpare.Copy
Selection(1).PasteSpecial Paste:=xlFormats
End If
rSpare.ClearFormats
set rSpare = Nothing
End Sub

Regards,
Peter

"GK80535" wrote in message
...

I'm trying to write a Macro that toggles through background color and
font styles.
No one's been able to help me out from my previous posts, so I'm still
searching.

I need a function that returns the current selection's font style (red,
italic, bold, times roman, etc.) so that I can store it in a variable
before my toggle begins and so that I can restore the style at the end
of my toggle. Can anyone please help me out with this?

Here's my code:

' Toggles the background color
' Keyboard Shortcut: Ctrl+Shift+O
Sub BackgroundToggle()
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 = False
Selection.Font.ColorIndex = 1
End If
End Sub


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

Hi
see your other post

--
Regards
Frank Kabel
Frankfurt, Germany


GK80535 wrote:
I'm trying to write a Macro that toggles through background color and
font styles.
No one's been able to help me out from my previous posts, so I'm
still
searching.

I need a function that returns the current selection's font style
(red, italic, bold, times roman, etc.) so that I can store it in a
variable before my toggle begins and so that I can restore the style
at the end of my toggle. Can anyone please help me out with this?

Here's my code:

' Toggles the background color
' Keyboard Shortcut: Ctrl+Shift+O
Sub BackgroundToggle()
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 = False
Selection.Font.ColorIndex = 1
End If
End Sub