Formatting a Cell

[HardAtWork]

I've been working on this for a while now...and I've gotten the form to work great on 2007. The problem comes in when trying to use the form in 2003.

Here's my goal.

Have a button that changes the color of the font and interior of the cells when selected and then change them back when it's selected again.

Works fine in 2007, but in 2003 I get a run-time error of 1004 stating that it can not change the interior of the cell.

Mode #1: 80% Gray interior and Green Font
Mode #2: White interior and Black Font

Here's a very short version of the coding:

If application.Version = "11.0" or "12.0" or "14.0" Then
ActiveWorkbook.Unprotect ("Password")
Application.ScreenUpdating = False

Worksheets("Page One").Select
Range("A1:ZZ100").Select
With Selection.Interior
.ColorIndex = 56
.Pattern = xlSolid
End With
With Selection.Font
.ColorIndex = 4
End With
If Me.CommandButton3.Caption = "Dark" Then
Me.CommandButton3.Caption = "Light"
Else
Me.CommadButton3.Caption = "Dark"
End If
End If
Application.ScreenUpdating = False
ActiveWorkbook.Protect ("Password")

If application.Version = "11.0" or "12.0" or "14.0" Then
ActiveWorkbook.Unprotect ("Password")
Application.ScreenUpdating = False

Worksheets("Page One").Select
Range("A1:ZZ100").Select
With Selection.Interior
.ColorIndex = 2 (This is where it states the problem is)
.Pattern = xlSolid
End With
With Selection.Font
.ColorIndex = 56
End With

End If
Application.ScreenUpdating = False
ActiveWorkbook.Protect ("Password")


The above coding is all together and I'm not sure that I know how to seperate it if I had to. I would like the coding to stay together as it make changing it easier on the person behind.


If ya'll could please help that would be great.

 

[Michael M]

In 2003 there isn't a

Range("A1:ZZ100")

try changing it to "IV100"

 

[AlphaFrog]

This is not the correct syntax to use OR in an IF Statement

If application.Version = "11.0" or "12.0" or "14.0" Then

It should be something like this...

If Application.Version = "11.0" Or _
   Application.Version = "12.0" Or _
   Application.Version = "14.0" Then

Or just this...

If CInt(Application.Version) >= 11 Then

I'm not entirely sure what you want to do in the code, but maybe try something like this...

    Dim rng  As Range
    
    ActiveWorkbook.Unprotect ("Password")
    Application.ScreenUpdating = False
        
    Worksheets("Page One").Select
    
    If CInt(Application.Version) >= 11 Then
        [COLOR="Green"]' Version 2007 or later[/COLOR]
        Set rng = Worksheets("Page One").Range("A1:[COLOR="Red"]ZZ[/COLOR]100")
    Else
        [COLOR="Green"]' Version 2003 or earlier[/COLOR]
        Set rng = Worksheets("Page One").Range("A1:[COLOR="Red"]IV[/COLOR]100")
    End If
    
    With Me.CommandButton3
    
        If .Caption = "Dark" Then
            .Caption = "Light"
            With rng
                .Interior.ColorIndex = 56
                .Interior.Pattern = xlSolid
                .Font.ColorIndex = 4
            End With
           
        Else
            .Caption = "Dark"
            With rng
                .Interior.ColorIndex = 2
                .Interior.Pattern = xlSolid
                .Font.ColorIndex = 56
            End With
        End If
    End With
        
    Application.ScreenUpdating = True
    ActiveWorkbook.Protect ("Password")

 

[HardAtWork]

I used the coding provided but it still hangs up on

Interior.ColorIndex = 2

The range I provided was just made up sorry about that.

basically what I want is when the interior of the cells are white and the text is black I want the command button to state Dark.

When the user clicks on the button it then changes the interior of the cells to 80% Gray and the font is bright green and the button then names itself Day.

The user needs to be able to click the button at any time to change the color of the worksheets. Once I get the code right for 2003, 2007, 2010 I will need make the same coding for multiple pages.

Does this help with what I'm wanting the code to do?

 

[AlphaFrog]

The above code works for me and I have Excel 2003. Is your "Page One" worksheet (not workbook) password protected? If yes, you may have to unprotect it first in the code to change its cell formatting.

As an example, this loops through all worksheets and Unprotects them, toggles the Font\Background colors for rows 1:100, then reprotects them with Password "Secret"

Private Sub CommandButton3_Click()

    Dim ws As Worksheet, ICI As Long, FCI As Long
       
    With Me.CommandButton3
    
        If .Caption = "Dark" Then
            .Caption = "Light"
            ICI = 56            ' Dark Gray background
            FCI = 4             ' Green Font Color
        Else
            .Caption = "Dark"
            ICI = 2             ' White Background
            FCI = 56            ' Black Font color
        End If
        
    End With
    
    Application.ScreenUpdating = False
    
    For Each ws In Worksheets
        ws.Unprotect "Secret"
        With ws.Range("1:100")
            .Interior.ColorIndex = ICI
            .Font.ColorIndex = FCI
        End With
        ws.Protect "Secret"
    Next ws
    
    Application.ScreenUpdating = True
        
End Sub