Help needed with conditional formatting

[peterv6]

I want to format cell A1 based on a combination of its contents, and those of cell C1.

Note: If not 0, A1's value will always be less than or equal to C1's value.

Example:
If A1 contains $0.00 and C1 contains $0.00, I want the fill color for both cells to be white, and the text color to be blue.

If A1 is $0.00 and C1 is $0.75 (or any value from a penny to 99¢), no change needed.

If A1 = C1, I want the fill color to be yellow and the text to be red for both cells.

I'm trying to use a formula to do this, but I haven't found anything that works.

The problem might be the formula I'm using in C1 to extract the cents from a money field & reformat it. B17 is a currency formatted field.

Any ideas would be greatly appreciated.

="$" & 0&RIGHT(B17,LEN(B17)-SEARCH(".",B17)+1)

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Is cell B17 an actual number or is it text? What does the formula =ISNUMBER(B17) return?

 

[peterv6]

Actually, I'm using Excel on Androif, so it'd be Microsoft 365 personal.

 

[Peter_SSs]

... so it'd be Microsoft 365 personal.

Thanks, that is the answer to my first question (& the answer would best be put into your account details as suggested so you don't get asked about your version every time you ask a question ), but what about the answer to my second question?

 

[peterv6]

Thanks, that is the answer to my first question (& the answer would best be put into your account details as suggested so you don't get asked about your version every time you ask a question ), but what about the answer to my second question?

Sorry, didn't notice second question, long night at work. The answer is TRUE.

 

[peterv6]

Sorry, didn't notice second question, long night at work. The answer is TRUE.

Actually, using the ISNUMBER function on cell C1 seems to show my problem. It turns out the result of the formula in C1 is in the form of $0.75, but ISNUMBER returns FALSE. Is there any way to convert that result into a number?

 

[Peter_SSs]

Thanks for updating your version details.

Is there any way to convert that result into a number?

There will be, but it depends on the actual question that I asked.

What does the formula =ISNUMBER(B17) return?

 

[peterv6]

Thanks for updating your version details.


There will be, but it depends on the actual question that I asked.

I did answer your question about B17:
peterv6 said:
Sorry, didn't notice second question, long night at work. The answer is TRUE.

 

[Peter_SSs]

The answer is TRUE.

OK, if B17 is a number, could this work for you?

Different formula is shown for column C. This column would be formatted as Currency with 2 decimal places. This displays the "$" sign but that sign does form part of the cell's actual value.
I have formatted column A the same and assume it contains numbers as well.

Then Conditional Formatting for columns A & C is shown below the mini sheet.
Check results for row 3 as I don't think you covered that scenario in your original descriptions. Or perhaps that is not possible with your data?

23 07 30.xlsm
	A	B	C
1	$0.00		$0.00
2	$0.00		$0.68
3	$0.66		$0.56
4	$0.56		$0.56
5
17		$23.00
18		$17.68
19		$14.56
20		$14.56
CF (2)

Cell Formulas
Range	Formula
C1:C4	C1	=MOD(B17,1)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1:A4,C1:C4	Expression	=AND($A1<>0,$A1=$C1)	text	NO
A1:A4,C1:C4	Expression	=AND($A1=0,$C1=0)	text	NO