Conditional Formatting on other Cell.

Hi Barry,

I totally understand your reply, but maybe I didn't explain what I want very well. It's different to what the above achieves.

Quite simply, if B1 contains anything at all, then A1 should go green.

The contents of A1 are irrelevant. Assume it will be empty.

Adam.

Adam,

If it was me, I wouldn't jump through hoops to format the key number. I would add a narrow column after column A and put a short letter code in it to signify the key status and format the new column according to the code. The new Column B will serve as a simple flag to catch the eye.

The expression in B could be as simple as:

=IF(LEN(E)>0, "R", "O")

"R" for Returned and "O" for Out. Testing Column E because we added a column in front of it.

Jerry

Hi,

appreciate the suggestion. But the way they use the key number is as follows:

As they often give out a key that has been previously used that day, they need to scan column A for the key number when checking someone out. If that box is coloured, they will ignore it and look further down.

If I was to use the adjacent column, I would simply wanna colour code it again rather than have a letter in it.

It's a lot easier for people to see when a cell has something in in when it is normally empty, but even better if it changes colour.

On the previous version we used to use a crosshatching to shade out the cell, so we could still see the number. This is very easy in Excel, but I can't find a way of doing it in Number, not even to just a colour.

Thanks.

Adam.

Adam,

What you put in the new Column B would be up to you. I was just suggesting a concept that you are free to modify.

If you don't want to display a character in, let's call it the Flag Column, B, you can set the Content color to the same as the Fill color, or you can use non-printing characters. For instance, one space for "Out" and two spaces for "Returned." Since the spaces don't show, you don't have to worry about what color they are in order to hide them.

Jerry

Unfortunately, numbers is not able to do that.

If you consider AppleScript to be part of the Numbers ecosystem, much as VB is part of Excel's, then I think you will find that this is very easily done in Numbers.🙂

Suppose, like Adam, you want to color red every cell in column A when there is a value in column D on that row. All you need to do is click somewhere in the target table and run a simple script like the one below (copy it into AppleScript Editor and click the green 'run' triangle).

SG

--colors cells in column A if there is a value in column D

--colors cells in column A if there is a value in column D

property colorColumnNum : 1 --> col A, the column to be colored

property dataColumnNum : 4 --> col D, the column with the data trigger

property startRow : 2 --> assumes 1 header row

tell application "Numbers"

set t to front document's active sheet's first table whose selection range's class is range

repeat with i from startRow to t's row count

tell t's row i

if its cell dataColumnNum's value is not missing value then set its cell colorColumnNum's background color to "red"

end tell

end repeat

end tell

--end of script

Sorry. Have received the "An error occurred while trying to submit your post. Please try again.

So I did. Twice.

Then sent the successful "test" message above.

Then encouraged by that success, again attempted the 'real' message.

Same error message. Twice.

Hence this message.

Will try again later this evening.

Regards,

Barry

I see you are using Number 2?

In that case this script below should work.

SG

--colors cells in column A if there is a value in column D-- for Numbers 2 only

property colorColumnNum : 1 --> col A, the column to be colored

property dataColumnNum : 4 --> col D, the column with the data trigger

property startRow : 2 --> assumes 1 header row

tell application "Numbers"

set t to front document's (first sheet whose tables's selection range's class contains range)'s first table whose selection range's class is range

repeat with i from startRow to t's row count

tell t's row i

if its cell dataColumnNum's value is not 0 then set its cell colorColumnNum's background color to "red"

end tell

end repeat

end tell

--end of script

If CELL A1 show a number above 50. CELL A3, A4, A5 will fill with Yellow.

Can Apple Numbers 09 customise a conditonal formationg base on other CELL ?

The script below will do that. Paste into AppleScript Editor. Click anywhere in table, then click the green triangle 'Run' button.

SG

--colors cells if value in A1 exceeds a trigger value

property dataTriggerCell : "A1"

property dataTriggerValue : 50

property rangeToColor : "A3:A5"

tell application "Numbers"

set t to front document's (first sheet whose tables's selection range's class contains range)'s first table whose selection range's class is range

if value of t'scelldataTriggerCell is greater than dataTriggerValue then

set t's range rangeToColor's background color to "red"

else

set t's range rangeToColor's background color to "white"

end if

end tell

--end of script

Still no luck at posting what I had written earlier. Getting the same error.

The solution is essentially what I offered in my initial reply to you question, and very similar to what Jerry has since posted (immediately following my trouble reports).

I added an auxiliary column, placing it after column E (Type), making it column F.

In F2, I entered this formula:

=IF(LEN(D)<1,"--",A)

and filled it down to the end of the column. Functionally, this formula is similar to the one Jerry has shown in his auxiliary column, B, except that the logic is reversed and it places two hyphens into the cell where D is empty, rather than the null string used in Jerry's example.

The conditional formatting rule for cells in column A compares the value in the cell with the value in the corresponding cell in column G. If they are the same (which they will be if a time has been recorded in column D) the rule applies a colour fill to the cell (in column A). The rule is the same as that shown in Jerry's example, except that it refers to G2 rather than B2.

Regards,

Barry

PS: Regarding the AppleScript solution(s) offered:

A solution using conditional formatting, and a formula to produce a match for the condition, will work automatically once it has been set up. No user action beyond entering the data is required.

A Script, or a Service created from that script, must be triggered either by running the script (from the Script Editor) or choosing the script or the Service from a menu. Either of these requires a user action in addition to the data entry.

B

Expression in Column B is:'

=IF(LEN(E)>0, A, "")

Format in Column A is as shown in Screen Shot.

Jerry

napolean,

Why wait? When you upgrade to Mevericks you are not forced to update to Numbers 3. And even if you do the previous version still exists (its moved to the folder "/Applications/iWork '09").

THANKS a million Ian. You solved the question :-)

I have to manually do it :-/

Thanks again, but I'm really looking to change toe colour of A based on the contents of D

There must be a way of doing this.

Adam.

This isn't difficult, and you've been given a couple of options. And, there are more.

I suggest that you post your own, new question on the topic and let someone work it out to your satisfaction.

Jerry

Yes Barry,

that's precisely what I need to do.

I'd be very happy for you to give me the instructions on how to do this.

Thanks.

Adam.