PDA

View Full Version : Solved: script works in Excel but not in 1-2-3



Keith_H
08-17-2004, 06:22 AM
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

:dunno
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)
08-17-2004, 07:07 AM
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
08-17-2004, 11:12 AM
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