Highlighting every other line in a spreadsheet - Statistica Visual Basic - Statistica - Dell Community

# Highlighting every other line in a spreadsheet

#### Highlighting every other line in a spreadsheet

I wrote a macro to highlight every other line in a spreadsheet.  This helps me not to get lost when reviewing my data and scrolling across the spreadsheet.  Does anyone have a more efficient way of doing it?  Feel free to use the macro if you would like.  It is copied below:

Sub Main

Dim nvar As Integer
nvar=S1.NumberOfVariables
S1.AddVariables "origional order", nvar, 1, scDouble, 8, -999999998, "General", "=v0+1"
S1.AddVariables "every other", nvar +1, 1, scDouble, 8, -999999998, "General", "=iif( v0/2=trunc(v0/2), 1,2)"
S1.Recalculate
'sort data by every other
S1.SortData(nvar +2)
'set cell formating to highlight grey
Set Cells=S1.CellsRange(1, 1, S1.NumberOfCases/2, S1.NumberOfVariables)
Set CellFont=Cells.Font
CellFont.Background=RGB(200,200,200)

'return data to origional order and delete variables
S1.SortData(nvar +1)
S1.DeleteVariables nvar +1, nvar +2

End Sub

All Replies
• Thanks for sharing Jennifer. This is a fun little macro. And I can see how it can help visualized data.

I ran this against the example dataset; adstudy. The top 50% turned grey. I decided that I wanted to mark the spreadsheet with four different colors. So I played around.

The macro below marks up the spreadsheed with four different colors. It isn't perfect. I was only working with spreadsheets that had even numbers of cases (100, 24).... numbers that had no remainder when divided by 4.

Sub Main

Dim nvar As Integer
nvar=S1.NumberOfVariables
S1.AddVariables "origional order", nvar, 1, scDouble, 8, -999999998, "General", "=v0+1"
S1.AddVariables "every other", nvar +1, 1, scDouble, 8, -999999998, "General", "=iif( v0/2=trunc(v0/2), 1,2)"
S1.Recalculate
'sort data by every other
S1.SortData(nvar +2)

Dim NumCases As Integer
NumCases = (S1.NumberOfCases/4)

'set cell formating to highlight grey, top 25%
Set Cells=S1.CellsRange(1, 1, NumCases, S1.NumberOfVariables)
Set CellFont=Cells.Font
CellFont.Background=RGB(200,200,200)

'set cell formating to highlight red, 26% to 50%
Set Cells=S1.CellsRange(NumCases + 1, 1, NumCases * 2, S1.NumberOfVariables)
Set CellFont=Cells.Font
CellFont.Background=RGB(200,20,20)

'set cell formating to highlight green, 51% to 75%
Set Cells=S1.CellsRange((NumCases * 2) + 1, 1, NumCases * 3, S1.NumberOfVariables)
Set CellFont=Cells.Font
CellFont.Background=RGB(20,200,20)

'set cell formating to highlight blue, 75% to 100%
Set Cells=S1.CellsRange((NumCases * 3) + 1, 1, NumCases * 4, S1.NumberOfVariables)
Set CellFont=Cells.Font
CellFont.Background=RGB(20,20,200)

'return data to origional order and delete variables
S1.SortData(nvar +1)
S1.DeleteVariables nvar +1, nvar +2

End Sub

• If you tried the macro and only the top half of the data were highlighted in grey, i have a suggestion.  An SVB command was added sometime recently so data can sort with or without formatting.  To sort with formatting in Version 9, you need SortDataEx().  SortData() will sort while leaving the formatting stationary.  I learned something new.

Here is the updated macro:

Sub Main

Dim nvar As Integer
nvar=S1.NumberOfVariables
S1.AddVariables "origional order", nvar, , , , , , "=v0+1"
S1.AddVariables "every other", nvar +1, 1, , , , , "=iif( v0/2=trunc(v0/2), 1,2)"
S1.Recalculate
'sort data by every other
S1.SortData(nvar +2)
'set cell formating to highlight yellow
Set Cells=S1.CellsRange(1, 1, S1.NumberOfCases/2, S1.NumberOfVariables)
Set CellFont=Cells.Font
CellFont.Background=RGB(200,200,200)

'return data to origional order and delete variables
'updated sort function to keep formatting
S1.SortDataEx(nvar +1)
S1.DeleteVariables nvar +1, nvar +2

End Sub

• I tried your macro, however an error was encoutered in the row

S1.Recalculate

, i.e. "Formula error. Unexpected sign"

could you explain it?

I am using the Statistica 10 Pl

apl

• Different regional settings require a different way of writing spreadsheet functions. I wrote this macro under US regional settings. Here, a comma is used to seperate agruments in the "iff" function. When a regional setting uses commas as decimal seperators in numbers, then semicolons are used to seperate arguements in the function.

My guess is this is the issue. So in the macro, this line:

S1.AddVariables "every other", nvar +1, 1, scDouble, 8, -999999998, "General", "=iif( v0/2=trunc(v0/2), 1,2)"

should be changed to this:

S1.AddVariables "every other", nvar +1, 1, scDouble, 8, -999999998, "General", "=iif( v0/2=trunc(v0/2); 1; 2)"

, changed to ; in the function. I think that should do it.

• Thank you very much, Jenifer, your macro works now.

Try my macro MultiANOVA (http://main3.amu.edu.pl/~apl/index.html)

Sincerely yours

apl