Scripting format of a column in Excel

I’m sure this is easy, but I’m beating my head against the monitor and even that isn’t helping! Okay here it is. I’ve got a column whose cell values are formulas. I need to determine if the formulas calculated values are negative and if so, color them red. Any help would be tremendously appreciated. This is what I’ve got:


tell application "Microsoft Excel"
repeat with CurrentRowLoop from 1 to 200
set CurrentRowCount to (CurrentRowLoop)
set Formula of Cell ("R" & CurrentRowCount & "C55") to "=RC[-43]-RC[-42]-RC[-34]-RC[-3]"

--this IF-THEN statement doesn't work...
if (value of Cell ("R" & CurrentRowCount & "C55"))  < 0 then

set ColorIndex of Font of Cell ("R" & CurrentRowCount & "C55") to 3.0
set Bold of Font of Cell ("R" & CurrentRowCount & "C55") to true
end if
end repeat
end tell

Is this what you mean?

tell application "Microsoft Excel"
	repeat with i from 1 to 200
		set the_cell to "R" & i & "C55"
		set Formula of Cell the_cell to "=R" & i & "C43-R" & i & "C42-R" & i & "C34-R" & i & "C3"
		if (get Value of Cell the_cell) < 0 then
			set ColorIndex of Font of Cell the_cell to 3.0
			set Bold of Font of Cell the_cell to true
		end if
	end repeat
end tell

Jon

This didn’t work. The value returned is always 0 regardless if the number was positive or negative.

Sorry, I edited my post entirely while you tested. This works for me in Mac OS X 10.3.2 and Excel X for Mac Service Release 1.

Jon

I’m running the same OS and same version of Excel.
I used the exact syntax you provided and I still get a value of 0 returned for all cells.
I even tried this, which didn’t work either. I’m stumped:


tell application "Microsoft Excel"
	repeat with i from 1 to 200
		set the_cell to "R" & i & "C55"
		set Formula of Cell the_cell to ("=L" & i & "-M" & i & "-U" & i & "-AZ" & i)
		if (get Value of Cell the_cell) < 0 then
			set ColorIndex of Font of Cell the_cell to 3.0
			set Bold of Font of Cell the_cell to true
		end if
	end repeat
end tell

What happens when you run this code?

tell application "Microsoft Excel"
	Activate
	Create New Workbook
	repeat with i from 1 to 10
		set Value of Cell ("R" & i & "C1") to i
		set Value of Cell ("R" & i & "C2") to "=R" & i & "C1*2"
		set Value of Cell ("R" & i & "C3") to "=R" & i & "C2*2"
		set Value of Cell ("R" & i & "C4") to 40
		set the_cell to "R" & i & "C5"
		set Formula of Cell the_cell to "=R" & i & "C4-R" & i & "C3-R" & i & "C2-R" & i & "C1"
		if (get Value of Cell the_cell) < 0 then
			set ColorIndex of Font of Cell the_cell to 3.0
			set Bold of Font of Cell the_cell to true
		end if
	end repeat
end tell

Jon

I sort of hate to mention it, but can’t you use an Excel number format to make negative numbers appear red?

Under number format choose Custom… and enter 0;[Red]-0

Can’t get bold this way, though.