BlockquoteTo be clear, it will NOT ask you to select a cell if it finds cells with the "red" RGB that match the default set in the Macro.
but in that case shouldn't it find the data with that color and change it to negative? I wasn't able to make it do that.
BlockquoteSorry, I misled you. The Video needs to be set for "unlisted" instead of "private". "Private" does not allow anyone but you to view the view.
no problem! now it should work
OK, that's great! I can now view the video, and the problem and solution are obvious.
Some how the KM Variable "DND_CCH__RedRGB" got set to "0, 0, 0".
The fix is to change it back to the value in the macro of "221, 46, 2".
There are several ways you can do this, but the easiest is to just click on the Gear Button on the Set Variable Action in the KM Editor, and select "Try Action"
This will set the variable back to the default.
Now the macro should run properly.
Please try again, and if there are any issues, another video would be great.
So i have the variable set back to default but it is still not running as expected. here is another video.
OK, thank for the video -- very helpful.
Must be something about the web page that I'm not modeling in my test page.
So, please select all of the cells on the web page, and manually copy then paste into an excel file, that you then zip and post.
I'll take a look at it and see if I can figure out what is going on.
Thank you! Here is the zip with the data as per your request.
Demo data.xlsx.zip (7.6 KB)
Thanks. That was very helpful.
I think I've identified the problem, and the solution:
So my Excel script could not process the data correctly.
is fairly simple:
Check for cells that either start with a "$" or end with a "%", remove them, and change to a number:
set curValNew to curVal
if (curVal starts with "$") then set curValNew to (text 3 thru -1 of curVal) as number
if (curVal ends with "%") then set curValNew to ((text 1 thru -3 of curVal) as number) / 100.0
if (curVal ≠ curValNew) then
set curVal to curValNew
set its value to curVal
set curValType to class of curVal
end if
property ptyScriptName : "Set Excel Red Cells to Negative Value V2"
property ptyScriptVer : "2.2" -- ADD Check for Numbers as Text, and Fix
property ptyScriptDate : "2020-09-04"
property ptyScriptAuthor : "JMichaelTX"
property LF : linefeed
set kmInst to system attribute "KMINSTANCE"
tell application "Keyboard Maestro Engine"
set redRGBStr to getvariable "DND_CCH__RedRGB" instance kmInst
end tell
set redRGB to my split(redRGBStr, ", ")
tell application "Microsoft Excel"
--activate
set cellList to (cells of selection)
--~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
--» Make Sure We Have a Valid RED RGB
--~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
set redColorFound to false
repeat with oCell in cellList
tell oCell
set curVal to its value
set curValType to class of curVal
set curValNew to curVal
if (curVal starts with "$") then set curValNew to (text 3 thru -1 of curVal) as number
if (curVal ends with "%") then set curValNew to ((text 1 thru -3 of curVal) as number) / 100.0
if (curVal ≠ curValNew) then
set curVal to curValNew
set its value to curVal
set curValType to class of curVal
end if
set cellColorRGB to its color of font object -- of oCell
if ((cellColorRGB = redRGB) and (curValType ≠ text)) then
set redColorFound to true
--exit repeat
end if
end tell -- oCell
end repeat
--~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
--» IF Red RGB Not Found
--~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
if (not redColorFound) then
--- Prompt User to Select a Red Cell ---
set msgStr to "Could NOT find any cells with this Red RGB Color: " & redRGBStr & ¬
LF & LF & "Select a cell with THE \"RED\" Color."
my displayKmAlert(ptyScriptName, msgStr)
--- Get the RGB of the Selected Cell ---
set {redCell} to (cells of selection)
set redRGB to color of font object of redCell
--- Update the KM Variable ---
tell application "Keyboard Maestro Engine"
setvariable "DND_CCH__RedRGB" instance kmInst to my join(redRGB, ", ")
end tell
end if
--~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
--» Loop Thru ALL Cells In Selection
--~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
set numRedCells to 0
repeat with oCell in cellList
tell oCell
set curVal to (its value)
set curValType to class of curVal
if (curValType ≠ text) then
set cellColor to its font color index of font object
set cellColorRGB to its color of font object
(*
Two Methods:
1. Test for Color ≠ Black (color index 1)
2. Test for Color = Red
*)
-- 1. Test for Color ≠ Black (color index 1) --
--if ((cellColor ≠ 1) and (curVal > 0)) then
-- 2. Test for Color = Red --
if ((cellColorRGB = redRGB) and (curVal > 0)) then
set numRedCells to numRedCells + 1
set its value to -1.0 * curVal
end if
end if
end tell -- tell oCell
end repeat
end tell
if (numRedCells > 1) then
set scriptResults to "OK"
else
set scriptResults to "[ERROR] No Red Cells were found with a RGB of " & redRGBStr
end if
return scriptResults
--~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
on split(pString, pDelim) -- @Strings @Lists
(* VER: 1.0 2016-02-25
---------------------------------------------------------------------------------
PURPOSE: Splits a String to Create an List (Array)
PARAMETERS:
• pString ┃ text ┃String to be split
• pDelim ┃ text ┃Delimiter to be used to split
RETURNS: ┃ list ┃ List of text items
AUTHOR: JMichaelTX
REF:
—————————————————————————————————————————————————————————————————————————————————
*)
set textDelimSave to AppleScript's text item delimiters
set AppleScript's text item delimiters to pDelim
set newList to text items of pString
repeat with aItem in newList
try
set contents of aItem to aItem as number
end try
end repeat
set AppleScript's text item delimiters to textDelimSave
return newList
end split
--~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
--~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
on join(pList, pDelim) -- @Lists @Strings
(* VER: 2.0 2016-02-25
---------------------------------------------------------------------------------
PARAMETERS:
• pList | list | List to be joined into string
• pDelim | text or list | Delimiter(s) to use in joining list
• May be one single string, or a list of strings
• List of two strings is used to first join the inner items into a list of strings
Then the 2nd delim is used to join the remaining items into a single string
Usually you would use {",", linefeed} to return multiple lines, with each line
representing one entity, like a screen.
AUTHOR: JMichaelTX
—————————————————————————————————————————————————————————————————————————————————
*)
set OTID to AppleScript's text item delimiters
set AppleScript's text item delimiters to pDelim
if (class of pDelim = list) then
set numDelim to count of pDelim
else
set numDelim to 1
end if
if numDelim > 1 then
--- Join Each Item (which has subitems) Using First Delimiter ---
repeat with oItem in pList
set contents of oItem to join(oItem, item 1 of pDelim)
end repeat
--- Now Join All Remaining Items with 2nd Delimiter ---
set newString to join(pList, item 2 of pDelim)
else
set newString to pList as text
end if
set AppleScript's text item delimiters to OTID
return newString
end join
--~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
on displayKmAlert(pTitle, pMsg)
set kmAlert to text 2 thru -2 of "
<dict>
<key>MacroActionType</key>
<string>Alert</string>
<key>SoundPath</key>
<string>/System/Library/Sounds/Hero.aiff</string>
<key>Text</key>
<string>[[ALERT_MSG]]
Click the \"Continue\" button when ready to continue.</string>
<key>TimeOutAbortsMacro</key>
<true/>
<key>Title</key>
<string>[[ALERT_TITLE]]</string>
</dict>
"
tell application "Keyboard Maestro Engine"
set kmAlert to search kmAlert for "[[ALERT_MSG]]" replace pMsg
set kmAlert to search kmAlert for "[[ALERT_TITLE]]" replace pTitle
do script kmAlert
end tell
end displayKmAlert
It works just as intended now! Outstanding work and VERY helpful!
is there any way to have it convert ONLY the numbers that end with a "%" to negative numbers?
if i can just make it keep all of the numbers in the "profit factor" row positive (regardless of color) then this will solve my problem completely!
Even if not, I can just go through and switch them manually and this should still be a good deal faster and more accurate than the method I was using. Thank you so much for all of your help! 
you could use a Regex that matches words ending with the % character,
save to variable,
then do something like Prepend Text command, and insert the word back.
Might be a good topic for a new thread
You think so? I was wondering if I should start a new thread but I figured I'd ask here first so people could understand the question in context, but you're right perhaps what I'm asking counts as a different issue entirely. Also I'm not sure how I should even phrase the question in the thread title 
by "Regex" do you mean the "search using regular expression" action?
search using regular expression
*nods *
Yes this can be done, but I think this could be a mistake.
I don't understand your complete workflow, but if ALL of the numbers in red are intended to be negative numbers, then, IMO, they should be set to a negative number.
If you are using these numbers in some other formula, and you always, or sometimes, want a positive value, then you should just get the absolute value of it.
Later on, you may come back to this spreadsheet to do other things with it, and if the, Total Profit, for example, is shown as a positive number, meaning profit was made, when in fact it was a loss, then that could lead to incorrect results/conclusions.
So, I'lll wait for your reply before I make any changes.
It's a fair point and I really appreciate your thoughtful approach. But I should note that not ALL of the numbers in red are intended to be negative numbers. Only those that end with a "%" (or, now that you mention it, begin with a "$"). The website has the numbers for Profit Factor appear red if they are less than 1 because profit factor = gross profits / gross loss, so a PF<1 means a negative net profit, but a PF<0 is impossible.
You're right that recording the red numbers for total profit etc. would be making them incorrect. But the total profit/loss is based on a projected starting capital that could grow or shrink between now and the time that this back testing project is completed and forward testing begins, which could be over a year. Until then the net profit percentage is crucial to keep track of but total profits are not IMO.
Perhaps the solution, if possible, is to make the script set ALL red numbers to negative IF they begin with a "$" OR end with a "%."
Can anyone tell me how to make the macro set ALL red numbers to negative IF they begin with a "$" OR end with a "%."? That seems to be the last hurdle to getting the data I need pasted correctly.
Patience, grasshopper. 
I'm very busy, but will try to get to this tonight.
OTOH, if anyone else can solve this, please do.
No rush at all, sir I figured you have plenty on your plate so I just wanted to throw it out there in case anyone else has an idea for a fix.