Column A to change font color if column B is not empty

D

dolphinv4

Hi,

I'd like to have column A of a sheet to automatically
turn to red font when a cell in column B is entered,ie,
the Cell A3 will change from red to black when B3 is
entered and B3 is a date.

Can it be done?

Thanks & Regards,
Val
 
F

Frank Kabel

Hi
try:
- select cells A3:A10
- goto 'Format - Conditional format'
- enter the formula
=$B3<>""
- choose a format
 
U

unlikeKansas

Check out Conditional Formatting

This will sort of work.

Highlight your cells in column A and use the formula =IF(ISNUMBER(B1),1,0)
in the Formula is box in Conditional Formatting. Set your Format to whatever
you require (e.g. red Font) and whenever a user enters a date in column B the
corresponding entry in Col A will turn red.

Unfortunately if the user enters a number in Col B the corresponding entry
in Col A will also turn red, however if they enter text it will not. Looks
like we need an ISDATE function.
 
D

Dolphinv4

Hi,

I tried the below but it doesn't seem to work like what I
wanted (maybe I did something wrong).

What I wanted was:

A B
$350.00 25/7/04
$500.00
$600.00 4/7/04


Column A is typed in whenever I send an invoice. Column B
will be filled in when I receive the payment. By default,
A will be in red font to show that they are unpaid. And I
require that as soon as B is entered, A will
automatically update itself as Black font.

If i use Conditional Formatting, it seems like I have to
keep doing the below steps you recommended to do the
update right?

Thanks & Regards,
Val
 
F

Frank Kabel

Hi
you may have to change the starting cell. I assumed cell A3. You can
change this (and change also the formula in the conditional format
dialog accordingly)
 
D

Dolphinv4

Hi,

yea, but it still doesn't work like what I require.

A1 will have to follow the value of B1, A2 will have to
follow the value of B2, etc.

I tried changing your formula to =$B:$B<>"" but it
doesn't work. Seems like the formula can only be linked
to a single cell?

Also, it does not refresh itself as and when I enter a
date into B rite? I have to go to "Format - Conditional
Formatting" all the time to refresh, which I do not want.
Is there any other way?


Thanks & Regards,
Val
 
F

Frank Kabel

Hi
if your first cell in the selected area (before going to the
conditional format dialog) is A1 then use the formula
=$B1<>""

Excel will adapt the row reference automatically. And of course you can
select several rows at the beginning. Just enter the formula for the
first cell reference with a relative row reference
 
D

dolphinv4

FINALLY got it! thank you very much!

Regards,
Val
-----Original Message-----
Hi
if your first cell in the selected area (before going to the
conditional format dialog) is A1 then use the formula
=$B1<>""

Excel will adapt the row reference automatically. And of course you can
select several rows at the beginning. Just enter the formula for the
first cell reference with a relative row reference


--
Regards
Frank Kabel
Frankfurt, Germany



.
Click to expand...
 
Top