hey all, i basically pasted together pieces of code i found online to make a script that colors the backgrounds of excel cells if the cell value is not equal to the value in row 2 of its column. it works perfectly fine, except it is very slow. it crawls cell by cell and applies the conditional formatting. you can watch it going. im wondering if there is a better approach. i used repeat loops to scroll through through each cell…but is there a faster way to do this? --to just “apply this conditional formatting to these cells”. i tried to select a range and then do something like “for cells in range…do this…” but the problem i was having was getting the row and column of the each cell, so i could adjust the formula for that cell. i couldnt figure it out. any advice or guidance would be great…thanks in advance for any help!!
tell application "Microsoft Excel"
set lastCol to count of columns of used range of active sheet
set lastRow to ((count of rows of used range of active sheet) + 1)
--c repesents current column number
set c to 3
repeat until c = lastCol
if (c mod 2) = 0 then
set c to (c + 1)
end if
set currCol to (get address of column c without column absolute)
set currCol to (characters 1 thru ((length of currCol) div 2) of currCol as string)
--r represents current row
repeat with r from 3 to lastRow
tell cell (currCol & r) of active sheet
try
delete (every format condition)
end try
set newFormatCondition to make new format condition at end with properties {format condition type:expression, formula1:"=$" & currCol & "$" & r & "<>$" & currCol & "$2"}
tell interior object of newFormatCondition to set color index to 6
end tell
end repeat
set c to (c + 1)
end repeat
end tell
You might use AutoFill.
This will copy the formatting of cells A3:B3 to A3:F300.
Unfortunatly, no matter how I fiddle with the type argument, it overwrites the values in the destination cells.
tell application "Microsoft Excel"
autofill (range "a3:B3") destination range "A3:B300" type fill formats
autofill (range "a3:B300") destination range "A3:f300" type fill formats
end tell
Another speed increaser would be to use R1C1 format to define the formula1 of the conditional Format “=(RC<>R2C)”
hi mike, thanks for the reply. unfortunately, i dont think it will help though. i cant use the same formatting copied to other cells. the goal is to have conditional formatting for each cell that compares its value to the value in the the 2nd row of its column. for example. cell A3 is compared against A2, if different, color the background of A3 yellow. A4 compared against A2, if different, color A4 yellow, etc, down to the last used cell in the A column. Then for the B column, B3 compared against B2, B4 compared against B2, etc. so each formula is different.
im guessing the way to do it is to select a range and then apply conditional formatting to all the cells in that range, using variables. the problem is i dont know how to get the row and column of each cell in that type of code block, which would then be used as the values for the variables in its formula.
anyone know how to do this or have any ideas?
many thanks!
You are mistaken about the conditional formatting being different. It IS the same formula.
Conditional Formatting formulas respond to relative/absolute addressing, just as cell value formulas do.
If you set CF on cell A4 with the formula =(A4=A$2) and copy the formatting to B5,
B5 will have the CF formula =(B5=B$2).
In R1C1 notation, both formulas are =(RC=R2C).
In the OP code, try changing this line
set newFormatCondition to make new format condition at end with properties {format condition type:expression, formula1:"=(RC<>R2C"}
or, if 2008 doesn’t accept R1C1 notation for CF formulas.
set newFormatCondition to make new format condition at end with properties {format condition type:expression, formula1:"=" & currCol & r & "<>" & currCol & "$2"}
Both will set Conditional Formatting to the first cell that can successfully be copied to other cells.
hey mike, thanks for the ideas. i just found something that did it. the whole thing completes instantly, a HUGE improvement. i pasted the code below. the only thing i cant figure out now is how to use this line in the new script: " delete (every format condition)". the original code i found on microsofts site reads:
tell range "B1" of active sheet
try
delete (every format condition)
end try
but no matter what i do, i cant seem to get it to work with a range instead of a single cell. instead of B1 id like to use myRange variable, but i even tried it with hardcoding the range “C4:C6” and it still wouldnt work. any ideas? thanks again.
tell application "Microsoft Excel"
set lastRow to ((count of rows of used range of active sheet) + 1)
set lastCol to count of columns of used range of active sheet
set lastCol to (get address of column lastCol without column absolute)
set lastCol to (characters 1 thru ((length of lastCol) div 2) of lastCol as string)
set myRange to range ("C4:" & lastCol & lastRow) of active sheet
--would like to remove any conditional formatting on the range at this point
set newFormatCondition to make new format condition at myRange with properties {format condition type:expression, formula1:"=C4<>C$3"}
tell interior object of newFormatCondition to set color index to 6
end tell
figured it out. heres the final script. works like a charm:
tell application "Microsoft Excel"
set lastRow to ((count of rows of used range of active sheet) + 1)
set lastCol to count of columns of used range of active sheet
set lastCol to (get address of column lastCol without column absolute)
set lastCol to (characters 1 thru ((length of lastCol) div 2) of lastCol as string)
set myRange to range ("C4:" & lastCol & lastRow) of active sheet
select myRange
try
delete (every format condition) of myRange
end try
set newFormatCondition to make new format condition at myRange with properties {format condition type:expression, formula1:"=C4<>C$3"}
tell interior object of newFormatCondition to set color index to 6
end tell
That’s odd, I tried your bulk delete conditional format syntax and it failed in most cases.
When I put Conditional Formatting on B2, C1, and C3 (leaving the A1:A3,B1,B3,C2 unCF’ed), this failed to remove the CF.
It only worked when every cell of A1:C3 had CF.
tell application "Microsoft Excel"
set myRange to get range "A1:C3"
try
delete (every format condition) of myRange
end try
end tell
I either had to loop, or to restrict the delete command to cells that had Conditional Formatting
tell application "Microsoft Excel"
set myRange to get range "A1:C3"
try
delete (every format condition) of (get special cells myRange type cell type all format conditions)
end try
end tell
awesome…nice improvement mike. thanks!
Thanks Mike and Rowie - I learned a lot from this thread.
I am trying to do something similar - I want a CF that compares a row cell and its predecessor. I am not clear on how to refer to a cell’s row predecessor–can this be done or must I use an autofill command? Thanks in advance for any guidance.
Model: iMac
Browser: Safari 533.16
Operating System: Mac OS X (10.4)
This is more of an Excel question than AppleScript.
Conditional Formatting formulas accept relative referencing, just as spreadsheet formulas do.
So
Select cell B2
Create Conditional Formatting with the formula =(B1=B2)
Then, if you copy that Conditional Formatting to another cell, that other cell will show the special format when its value is equal to the value of the cell above it.
Other Excel questions could be addressed to the forums at http://www.mrexcel.com or http://www.excelforum.com