Well I’m back again! I’ve found the tutorials on this forum really helpful, and the reply I got to my previous post was genius and it helped solve a big problem for me, so I figured I’d try my luck once more.
I know some find/replace script. I know how to change a cell’s colour. But what I don’t know, is how to combine them to do one, very specific task, so I’m hoping you guys can help out one more time.
I have a spreadsheet with 4 columns, A through D. In column D, every couple of cells (but not every cell), there is the word “correct.” What I want my script to do is this:
search through column D and find every instance of the word “correct”
when it identifies the word “correct” I want it to highlight the entire row yellow (across columns A through D, but only the specific row that “correct” shows up in. So if cell D4 has the word “correct” I want the script to colour cells D4, C4, B4, and A4.)
when it encounters a cell without the value “correct” I want it to do nothing, and continue its sequential search for the word “correct” and when it encounters it, highlights the row it lives in, from A to D, yellow.
I’m fairly sure this is possible, but I think it’s calling on something specific that maybe I haven’t learned how to do yet. Any help would be really appreciated, thanks everyone!
EDIT: Sorry I neglected to mention specifically that this was for Excel.
Thanks for your help! I tried to incorporate it but when I run the script nothing actually happens (no error messages, but also no changes to any cell colours either). This is the script I have so far.
tell application "Microsoft Excel"
tell active workbook
try
set theRange to find column 4 what "correct" look in values look at whole search order by rows without match case and match byte
on error
set theRange to missing value
end try
if theRange is not missing value then
tell interior object of theRange
set color to {0, 255, 0}
end tell
end if
repeat until theRange is missing value
try
set theRange to find next column 4 after theRange
on error
set theRange to missing value
end try
if theRange is not missing value then
tell interior object of theRange
set color to {0, 255, 0}
end tell
end if
end repeat
end tell
end tell
Also, doesn’t workbook 1 specifically call on an open workbook that’s currently called “workbook 1”? If so, then that won’t work for my entire script, as I’m trying to design it so that it can format any open workbook when it’s run.
Third, I ran this script (both with my active workbook and a workbook titled ‘workbook 1’) and it never stopped (arguably it never even started, nothing changed, it just kept running for 6+ minutes until I shut it down)
tell application "Microsoft Excel"
tell workbook 1
tell worksheet 1
try
set theRange to find column 4 what "correct" look in values look at whole search order by rows without match case and match byte
on error
set theRange to missing value
end try
if theRange is not missing value then
tell interior object of theRange
set color to {0, 255, 0} -- green
end tell
end if
repeat until theRange is missing value
try
set theRange to find next column 4 after theRange
on error
set theRange to missing value
end try
if theRange is not missing value then
tell interior object of theRange
set color to {0, 255, 0} -- green
end tell
end if
end repeat
end tell
end tell
end tell
It doesn’t work with workbooks, find works with sheets not work with workbooks.
Workbook 1 is the first workbook opened that is still open. So when you open three documents and closes the first, workbook 1 will refer to the second document since it is the first open document. You can also refer to workbook by it’s title using a string instead of integer identifier. like:
tell workbook "Workbook1"
.
end tell
If you want to work with the front most sheet you can use active sheet
tell application "Microsoft Excel"
tell active workbook
tell active sheet
.
end tell
end tell
end tell
Here the complete script:
tell application "Microsoft Excel"
tell active workbook
tell active sheet
try
set theRange to find column 1 what "correct" look in values look at whole search order by rows without match case and match byte
on error
set theRange to missing value
end try
if theRange is not missing value then
set matchedRow to first row index of theRange
tell interior object of range ("$A$" & matchedRow & ":$D$" & matchedRow)
set color to {255, 0, 255}
end tell
end if
repeat until theRange is missing value
try
set theRange to find next column 1 after theRange
on error
set theRange to missing value
end try
if theRange is not missing value then
tell interior object of range ("$A$" & first row index of theRange & ":$D$" & first row index of theRange)
set color to {255, 0, 255}
end tell
if first row index of theRange < matchedRow then exit repeat
set matchedRow to first row index of theRange
end if
end repeat
end tell
end tell
end tell
I managed to catch my workbook v. worksheet error right as you posted this. Next step is to get the highlighting to stop, and not go beyond the cell with the word “correct” in it. I’ll give that a swing this afternoon!
I implemented that in the code above as well It keeps track if the first row index of the previous match is not bigger than the current match.
Update 1: I read it again and I think I misunderstood, you meant not exceed column 4? Update 2: I updated the code above which makes a range to column 4 and set background ground color to purple (for testing purpose)
It keeps track if the