Script works in Excel but not in 1-2-3

[Keith_H]

Hi there,

I've developed a very basic script in Excel. (It selects a small range, and for each cell, it changes the background colour based upon what is typed in the cell, red for R, yellow for Y, green for G, otherwise blank).

I can't get a similar script to work for a 1-2-3 spreadsheet, though. Can anybody point out where I'm going wrong. (I know that the 1-2-3 script language is not 100% common with VBA as used in Microsoft Excel).

Your help will be appreciated. Thanks.
Keith


Excel script:

Sub Part1()
    Range ("O9:Y56").Select
End Sub
Sub Part2
    For Each cell In Selection
         If cell.value = "R" Then
              cell.Interior.ColorIndex = 3
         If cell.value = "Y" Then
              cell.Interior.ColorIndex = 6
         If cell.value = "G" Then
              cell.Interior.ColorIndex = 4

1-2-3 script

error: not a collection object

Sub test01

[Overview Region:O9..Overview Region:Y56].Select 
Forall dave In Selection
If dave = "G" Then
Selection.Background.Backcolor.Colorname = "leaf green" 
Elseif dave = "Y" Then
Selection.Background.Backcolor.Colorname = "yellow"
Elseif dave = "R" Then
Selection.Background.Backcolor.Colorname = "light salmon"
End If 
End Forall
End Sub

[Richie(UK)]

Hi Keith,

Welcome to the forum.

As regards your Excel code you may like to consider using the Select Case statement (full details in the Help files but ask if you need clarification) - this will speed-up your code by avoiding the multiple If Then evaluations for the various colours.

I'm not familiar with the 1-2-3 equivalent of VBA. However, one thought - does it have the equivalent of Excel's macro recorder? This could help you with a lot of the syntax issues that you may face.

[Keith_H]

Hi Richie,

Got it solved, the structure is pretty similar:

Sub TEST01
        [O9..Y56].Select 
        Forall onecell In Selection.Cells
                If onecell.CellValue= "G" Then
                        onecell.Background.Backcolor.Colorname = "leaf green" 
                Elseif onecell.CellValue = "Y" Then
                        onecell.Background.Backcolor.Colorname= "yellow" 
                Elseif onecell.CellValue = "R" Then
                        onecell.Background.Backcolor.Colorname = "light salmon" 
                End If
        End Forall
End Sub

Cheers. Bye for now