Conditional format, cell format

alliswell

Board Regular
Joined
Mar 16, 2020
Messages
217
Office Version
  1. 2007
Platform
  1. Windows
  2. Mobile
Hi everybody
H I J K
1 83 40

2

3

in j1 i am using this formula
=if(i1="","",if(i1=0,"PAID",IF(I1="-","PENDING",IF(i1<h1,i1-h1&" Balance",""))))

now, j1 gets result -43 Balance. so i want this negative number in red color and balance in black and or both in red. i tried with conditional format and cell format but color didnt changed to red.

thanks and regards
 
i want this negative number in red color and balance in black ..
If the cell contains a formula, which yours does, you cannot do that.


.. or both in red

25 02 23.xlsm
HIJ
18340-43 Balance
CF Red
Cell Formulas
RangeFormula
J1J1=IF(I1="","",IF(I1=0,"PAID",IF(I1="-","PENDING",IF(I1<H1,I1-H1&" Balance",""))))
Cells with Conditional Formatting
CellConditionCell FormatStop If True
J1Expression=LEFT(J1,1)="-"textNO
 
Last edited:
Upvote 0
If the cell contains a formula, which yours does, you cannot do that.




25 02 23.xlsm
HIJ
18340-43 Balance
CF Red
Cell Formulas
RangeFormula
J1J1=IF(I1="","",IF(I1=0,"PAID",IF(I1="-","PENDING",IF(I1<H1,I1-H1&" Balance",""))))
Cells with Conditional Formatting
CellConditionCell FormatStop If True
J1Expression=LEFT(J1,1)="-"textNO
Thanks peter its working.
But a minor issue. Its not effecting in my j1:j500.
In conditional format i put like this =left(j1:j500,1)="-"
But not working
 
Upvote 0
In conditional format i put like this =left(j1:j500,1)="-"
That is not the correct way.
  1. Remove all the conditional formatting from col J
  2. Select J1:J500
  3. In Conditional formatting put the formula =LEFT(J1,1)="-"
Excel will automatically adjust the formula for all the other rows
 
Upvote 0
That is not the correct way.
  1. Remove all the conditional formatting from col J
  2. Select J1:J500
  3. In Conditional formatting put the formula =LEFT(J1,1)="-"
Excel will automatically adjust the formula for all the other rows
Ok. Thanks thanks a lot bro
 
Upvote 0

Forum statistics

Threads
1,226,883
Messages
6,193,488
Members
453,803
Latest member
hbvba

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top