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
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
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
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