Hey guys, I’m new to the forum, but I’ve run into a bit of an issue trying to convert a VBA script I just finished to Applescript. About a few weeks after I got a spreadsheet setup in Excel with VBA I found out we’re making the switch to the newer Office version, which as most of you know does not include VBA. I’m in a time crunch to get it finished before the end of the month for the new grading session, so any help to point me in the right direction would be very much appreciated. I’ll keep searching help docs as well.
In a nutshell, the script will check what your active cell is, then change cell formatting on all cells before it in it’s particular row and column. Think of it like highlighting your columns and rows so it’s easier to see what corresponding row/column you’re in. It updates every time you go to a new cell.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim iColor As Integer
Dim iFont As Integer
'// Note: Don’t use if you have conditional
'// formatting that you want to keep
'// On error resume in case user selects a range of cells
On Error Resume Next
iColor = Target.Interior.ColorIndex
iFont = Target.Font.ColorIndex
'// Leave On Error ON for Row offset errors
iColor = 10
iFont = 2
Cells.FormatConditions.Delete
'// Horizontal color banding
With Range(“A” & Target.Row, Target.Address) 'Rows(Target.Row)
.FormatConditions.Add Type:=2, Formula1:=“TRUE”
.FormatConditions(1).Interior.ColorIndex = iColor
.FormatConditions(1).Font.ColorIndex = iFont
End With
'// Vertical color banding
With Range(Target.Offset(1 - Target.Row, 0).Address & “:” & _
Target.Offset(-1, 0).Address) 'Rows(Target.Row)
.FormatConditions.Add Type:=2, Formula1:=“TRUE”
.FormatConditions(1).Interior.ColorIndex = iColor
.FormatConditions(1).Font.ColorIndex = iFont
End With
End Sub
Thanks for any help! 