You can make a difference in the Apple Support Community!

When you sign up with your Apple Account, you can provide valuable feedback to other community members by upvoting helpful replies and User Tips.

Looks like no one’s replied in a while. To start the conversation again, simply ask a new question.

formula changing text colour of different cells, effect another cell's value.

Hi everybody,


I have a question about a formula.

I have 2 columns and 2 rows.

The A1 cell contains 20% and B1 cell contains 30% and the A2 cell has a number 100 in it.

Would it be possible to create a formula that would change the value of the A2 cell depending on the colour of the text in A1 or B1 cell?

e.g.: if I make the A1 cell text black (20%) and B1 cell white (30%) than it would effect the value of the A2 cell and turn the original value 100 to 20. Or if I turn B1 cell text black (30%) and A1 cell white (20%) than it would change the value of A2 cell from 100 to 30?


Thank you so much!

I hope you can help me!

Peter

MacBook Pro 15″, macOS 10.15

Posted on Sep 21, 2020 1:59 PM

Reply
Question marked as Top-ranking reply

Posted on Sep 21, 2020 2:46 PM

No. There are no formulas or settings that will change the color or value of a cell based on manually chosen colors in other cells. It is not possible for the value in a cell that was manually typed in to change unless something new is typed or pasted into it. The exception to this is an Applescript.


If the cell A2 has a formula in it, though, it can be 100% under one set of conditions (when A1=20% and B1=30%) and 30% under another set of conditions. The color of the cell can change based on whether it is 100% or 30% by using conditional highlighting. Still, the color of other cells cannot be used as a condition for conditional highlighting.

3 replies
Question marked as Top-ranking reply

Sep 21, 2020 2:46 PM in response to Algernom

No. There are no formulas or settings that will change the color or value of a cell based on manually chosen colors in other cells. It is not possible for the value in a cell that was manually typed in to change unless something new is typed or pasted into it. The exception to this is an Applescript.


If the cell A2 has a formula in it, though, it can be 100% under one set of conditions (when A1=20% and B1=30%) and 30% under another set of conditions. The color of the cell can change based on whether it is 100% or 30% by using conditional highlighting. Still, the color of other cells cannot be used as a condition for conditional highlighting.

Sep 21, 2020 3:08 PM in response to Algernom

As BadUnit stated, there is no automatic conditional formatting that will do this - you can set formatting based on a cell's value, but not on its attributes - but it can be done via AppleScript.


For example, this script does what you ask - but you need to invoke it manually (it won't auto-update based on the changing values of the cells). Just past it into a new Script Editor document and run it. It should be fairly self explanatory, but post back if you need more help.


set blackColor to {0, 0, 0}
set whiteColor to {65528, 65533, 65524} -- I have no idea why Numbers uses this for White, but...

tell application "Numbers"
	tell table 1 of sheet 1 of document 1
		set a1 to cell "A1"
		set b1 to cell "B1"
		
		if text color of a1 is blackColor and text color of b1 is whiteColor then
			set value of cell "A2" to (get value of a1)
			
		else if text color of b1 is blackColor and text color of a1 is whiteColor then
			set value of cell "A2" to (get value of b1)
		end if
	end tell
end tell


formula changing text colour of different cells, effect another cell's value.

Welcome to Apple Support Community
A forum where Apple customers help each other with their products. Get started with your Apple Account.