Excel 2002 (XP)

  osben 17:00 06 May 2003
Locked

Hi
I want to be able to conditional format involving cells in two columns I know how to use the conditional formatting but that will not produce the result I am looking for.

Two columns - (Col 1 & Col 2) want to change colour of the font in Col 1 if the contents of Col 1 are the contents of the cell in Col 2 and also change colour of font in Col 1 if contents of Col 1 are > the contents of the cell in Col 2

As I have said, I know how to conditional format if the formatting only covered one cell/column but this involves two and I can see no way of utilising conditional formatting for what I want.

Hope the above makes sense and would appreciate any help.

  VoG™ 17:24 06 May 2003

Select Cell A1, Format/Conditional formatting

Under Condition 1 select "Formula is" and in the formula box enter

=A1+B1

Click the Format button and choose colour

Then click Add>> button, under Condition 2 select Formula is and enter

+A1>B1
Format button, select colour.

Then click the Format painter toolbar button (paintbrush) and select the range of cells in Column A that you want to apply to.

  Megatyte 17:47 06 May 2003

It should be

=A1=B1

=A1>B1

  VoG™ 17:48 06 May 2003

.

  osben 09:39 07 May 2003

Thanks for the suggestion VoG™, I will give it a try now.

  osben 11:49 07 May 2003

Thanks for the info VoG™ I did have to change the first conditional format line to A1<B1 to get it but it works perfectly now.

If I could tax your brain a bit more!!!

If I put a Zero in cell A1, I would want it to stay Black. Not change colour in other words. At the moment if a zero is added to cell A1 and Cell B1 is a positive figure the font in Cell A1 changes colour. I therefore need to exclude the figure 0.00 in my conditional formatting.

Any ideas on how I can incorporate this into the two Cond. Format formulae you suggested.

Thanks once again

  VoG™ 12:08 07 May 2003

You need to set this as the first condition. In the Conditional Format dialog box, for Condition 1 select ""Cell value is", in the next box select "Equal to" and in the next box enter 0. Then click the Format button and select the black font.

You then need to add the two other conditions.

It will not work unless you set the 0 condition as "Condition 1".

  osben 12:46 07 May 2003

Many thanks
It works great. The zeros now stay black but after all your help, dare I ask you why a number in Cell A1 which is the SAME AS a number in Cell 2 now changes colour to the identify colour set up for the < conditional formating when it did stay black as I wanted it to.

Regards

  VoG™ 13:01 07 May 2003

I think that it is because you used

=A1<B1 instead of

=A1=B1

as what will now be Condition 2.

  osben 09:53 11 May 2003

Unfortunately that does not seem to be the reason VoG™

If you or anyone else can throw any further light on the problem I would appreciate it.

  VoG™ 14:30 11 May 2003

I'm a bit baffled to be honest. In your original post you stated "want to change colour of the font in Col 1 if the contents of Col 1 are the contents of the cell in Col 2". Thus the condition should be

=A1=B1

But you have used

=A1<B1

which will change the colour of the font if the contents of Col 1 are LESS THAN the contents of Col 2.

I would suggest that you check carefully the formulas that you have set for the three conditions. Make sure for example that Excel has not changed =A1=B1 to =$A$1=$B$1.

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

OnePlus 5 review

Alice Saey's mesmerising animation for Dutch singer Mark Lotterman

iPad Pro 10.5in (2017) review

Comment booster votre iPhone ?