Format Painter button problem

S

Skip Bisconer

When I wish to format several cells the same I double click the format
painter and I am able to format each cell without reclicking the button. I
have just upgraded to Excel 2003 and now I can only do that on a worksheet
that hasn't any VBA code attached. Is this a bug? I know other people on Mr
Excel are having the same problem and no one seems to have an answer. Thanks
for any response or help you may give me.
 
G

Gord Dibben

Skip

Depends upon the VBA code attached, I would imagine.

If you have worksheet event code that formatted cells, this formatting would
overwrite what you painted with the format painter.

Example...........

Option Compare Text
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Num As Long
Dim rng As Range
Dim vRngInput As Variant
Set vRngInput = Intersect(Target, Range("A:A"))
If vRngInput Is Nothing Then Exit Sub
On Error GoTo endit
Application.EnableEvents = False
For Each rng In vRngInput
'Determine the color
Select Case rng.Value
Case Is = "A": Num = 10 'green
Case Is = "B": Num = 1 'black
Case Is = "C": Num = 5 'blue
Case Is = "D": Num = 7 'magenta
Case Is = "E": Num = 46 'orange
Case Is = "F": Num = 3 'red
End Select
'Apply the color
rng.Interior.ColorIndex = Num
Next rng
endit:
Application.EnableEvents = True
End Sub

Copy/paste to a worksheet module(right-click on a sheet tab and "view code").

Enter any of the letters a through f in column A.

Color B1 yellow and select it.

D-click on Painter and paint column A.

What is the result?

Gord Dibben Excel MVP
 
D

Debra Dalgleish

The format painter seems to work as expected in Excel 2003.

What kind of code is attached to the sheets in which you're having a
problem?
 
S

Skip Bisconer

I had some code to format text to red if amounts were lower than 0. I have
since changed to condition formatting to accomplish this task rather than
VBA. As soon as I removed them the double click process worked fine. I have
tried some of my other older worksheets that had similar formating cells with
code and the double click works on those. Mysitical? I have Peasons functions
for changing text colors based on value and never had a problem before. I
guess it was just me.

Thanks for your help. It did solve my problem.:)
 
Top