[Closed] Excel - conditional formatting problem.

use the relative lock in the conditional format.

In conditional format, set condition to "Formula is" "=A1=$B$1"

you can then copy that format down from A1 to A5 (it will follow as "A1" is unlocked, but it will keep to the absolute $B$1 ref)

Stoner... that was quick... thanks... runs off try it out...

Stoner, that is ace - it only bloody well works - thanks!

BUT - my problem is a little more complicated than that - just wanted to try and get a simple version to work first.

The column of numbers is actually a few columns long (a 'range'? Is that the right term?).

So the range of numbers is say A1:D5, and the number that needs comparing is in E1.

I have just tried "A1:D5=$E$1") and that doesn't work...?

Again, thanks if you can help?

you dont need to spec the array in the formula. Just go to A1, and set the conditional formula to: "=A1=$E$1"

When you copy and paste the format from A1 to A1:D5 it will adapt the conditional formula for each cell to the cell it is in, but continue to compare it to E1 (which is locked with $) - so after copy and pasting, if you went into C3 and checked the conditional format, youd find it said: "=C1=$E$1"

EDIT: Just to check what you mean: do you want the format to apply to A1, B1, C1 and D1 when A1 = E1 or do you want A1, B1, C1 and D1 to be formatted when they independently match E1?

AM off to bed now. If you are after the latter above, then my first para shows how to do that. If the former then you'll need something else.

Stoner - that is brilliant and SO simple - I have been over thinking this a little too much! Thank you.

prego.

Stoner (just in case you see this again)...

The conditional formatting rule evolved a little last night to become:

=(AND($C$15=[u]1[/u],C3=$P$15)

[with the underlined '1' being formatted to 'red', and another rule being the same but with a '2' and that would be 'blue' etc].

Is there an easy way to copy all of these rules and apply then the subsequent cells in the 'P' column - so $P$16, $P$17 etc (the rest will all stay exactly the same).

Hope that makes sense?

I'm not entirely sure which bit you want to copy but if you change where the $ lock signs appear when you copy the formatting to a new cell the references will change accordingly.

as jfletch says, you need to be a bit clearer about which are cells are you fixed comparisons and which ones are the dynamic cells youre testing.

Hello!

Sorry for the delay - I was out of the office for a short while.

lets keep it simple, the present formula is:

=(AND($C$15=1,C3=$P$15) with the 'C3' being the range that is being compared to 'P15'.

What I want to do is then make the same formula but to then compare it to P16 (to compare the numbers) and the 'C15' would change to 'C16' to check the formatting colour), so it would be:

=(AND($C$16=1,C3=$P$16)

I guess I am simply trying to save myself from writing out 200 conditional format rules (4 rules per comparison, and 50 comparisons).

=(AND(C15=1,$C$3=P15) and copy if I understand correctly

EDIT: Read up about using $ signs they are very handy when comparing to numbers or dates in column and row headings

For instance if you wanted the same formatting in column D but still looking at column p but instead use d3 for comparison you would write the formula as follows and copy

=(AND(C15=1,C$3=$P15) if you copy this across one column if becomes =(AND(D15=1,D$3=$P15) but if you copy down one row it is =(AND(C16=1,C$3=$P16)

mefty - thank you! I will try that soon and will report back.

Hmmm, I wonder if I am not explaining this last bit correctly - which is likely the case.

Lets try again,

I am asking two questions and if both are 'Yes' then I want to highlight the matching cell.

Question 1. Is the number in cell P15 the same as a number in cells C3:O12 [it will be, if that matters]

Question 2. P15 has another cell associated with it (C15), which will contain either 1,2,3 or 4. The number in this cell determines what colour to make the cell in C3:O12. (I have a separate rule for each colour - 1 - red, 2 = yellow, 3 = green, 4 = black)

So right now I have four formulas:

=AND($C$15=1,C3=$P$15) [red]
=AND($C$15=2,C3=$P$15) [yellow]
=AND($C$15=3,C3=$P$15) [green]
=AND($C$15=4,C3=$P$15) [black]

And these format formulas have been copied to all cells in the C3:O12 range of cells.

I am trying to save myself typing the above formulas out for each pair of question cells (i.e., column C and column P).

Is that explained any better?

what changes in $C$15 to make it change to 1, 2, 3 or 4?

Stoner - they are simply 'categories' in excel terms (they are reagents used in a lab in reality).

So do you manually type 1, 2, 3 or 4 into C15 and want to see what happens?

Stoner - in a way, yes. But they are typically inputted in a 'batch' so a load of 1's may go in together (say in C15:C20) with an associated value in the P column (P15:P20). So we are matching the number in P15 with a number somewhere in C5:O15 and colouring the cell in C5:O15 relative to the number in C15. One rule for each colour at present.

you can only have 3 conditional formats in Excel 2003.

I think it's unlimited in 2010

Yep - I am just trying to avoid writing them all out one at a time.

It won't let me either copy or paste the formula(s), which is a little annoying as I could just change the numbers.

you dont need to type them out.
Just deal with C3 first as:

=AND($C$15=1,C3=$P$15) [red]
=AND($C$15=2,C3=$P$15) [yellow]
=AND($C$15=3,C3=$P$15) [green]
=AND($C$15=4,C3=$P$15) [black]

Then copy C3, select C3:O12 and paste/special/format

I think you need four rules for each cell in the range as follows

=c3=$p$15 then format red
=c3=$p$16 then format yellow
=c3=$p$17 then format green
=c3=$p$18 then format black

And 1,2,3 and 4 are entered into P15 to P18.

Then copy format across range to o12.

yep - done that.

But then I need to do:

=AND($C$16=1,C3=$P$16)
=AND($C$16=2,C3=$P$16)
=AND($C$16=3,C3=$P$16)
=AND($C$16=4,C3=$P$16)

Then:

=AND($C$17=1,C3=$P$17)
=AND($C$17=2,C3=$P$17)
=AND($C$17=3,C3=$P$17)
=AND($C$17=4,C3=$P$17)

etc etc

why are they all referencing C3?

if it's not confidential, youc an always email me the sheet and I can have a better look as I dont think I quite get what youre trying to do

I am inputting the conditional format rules into C3, and then copy and pasting the rules to C3:O15

Stoner - that would be awesome. It is medical data though so I can't send it all over, so if it is OK, I will make a similar example with some made up data and send it over?

I really appreciate all your help!

sorry, that doesnt make any sense to me.

C3 is the first cell in an array that is being conditionally tested against $P$15. And then depending on the state of $C$15 it will show a different colour.

I assumed that if you went down or along from C3, then you are still testing the contents of your cell (say D4) against $P$15 and also the state of $C$15. Is that not the case?

OK, send over a clean one. Ill got put the kettle on.

I will make it a (slightly) simpler version as well!

Have you not just got an array of numbers which can be 1, 2, 3 or 4 or something else and if they are 1,2,3 and 4 you want them to be highlighted in different colours depending upon which of the 4 they are? if so what I suggested works as I have just done it.

no I think he has two simultaneous conditions in four sets for each one, but what I dont get is the movement along the 15th row. So will see with the example workbook.

Stoner - you are correct. Workbook sent. Hope it now makes sense.

Must admit I am completely confused, I thought it was two conditions but then it appeared to be only one - probably makes sense to colour code it if you are having problems explaining it!