Excel: Conditional Formatting

[Marc]

I've got a number of cells that I want to add conditional formatting to. Problem is that not all of them have the same number formatting. I don't really want to have to do them all individually, is there a way to do just one and copy8 and paste the Conditional formatting into the rest?

The only answers I can find when I search tell me to use format painter but that will paste the number formats as well.

 

[Tori_T]

It's been a while since I did something like this, but IIRC the "Paste Special" option pops up a load of tick boxes so you can select what aspects of the copied data you want transferred.

ETA: Could you select all the relevant cells and then go into the conditional formatting box?

 

[mark4183]

^^^ as above, there should be a paste special option

 

[Marc]

It's been a while since I did something like this, but IIRC the "Paste Special" option pops up a load of tick boxes so you can select what aspects of the copied data you want transferred.

^^^ as above, there should be a paste special option

There is "All merging conditional formats" but that copies the formulae over as well.

Could you select all the relevant cells and then go into the conditional formatting box?

That was my other option but my finger slips off the ctrl key, usually when I get to the last few cells!

 

[Tori_T]

That was my other option but my finger slips off the ctrl key, usually when I get to the last few cells!

I've never used it, but would turning on the sticky keys function help with that?

 

[Marc]

I've never used it, but would turning on the sticky keys function help with that?

Sticky keys?

 

[mark4183]

noooooooooooo.... I hate sticky keys hahaha

there should be an option in the paste special bit.... or try going into the conditional formatting rules section and selecting cells, columns or rows

 

[Marc]

Sticky keys?

Just did a search. If I switch it on, it allows me to select a 2nd cell but, if I select a 3rd, it loses the first 2.

 

[Tori_T]

Control Panel >> (Ease of) Access Center >> Make Keyboard easier to use.
There's an option to make CTRL/ALT/DEL keys latch until you press the next key, so you can get functions like CTRL+C without having to press and hold the CTRL key.

 

[Marc]

there should be an option in the paste special bit

As above, the only available option pastes the formula as well.

or try going into the conditional formatting rules section and selecting cells, columns or rows

Can't see anything there, using Excel 2010.

 

[Tori_T]

It doesn't make the CTRL key act like shift-lock; you have to tap it before each selection and it'll hold until you press the next key, then turn off

 

[Marc]

It doesn't make the CTRL key act like shift-lock; you have to tap it before each selection and it'll hold until you press the next key, then turn off

Ah, right, wiv ya now.

Edit: Just tried it with 10 cells, I hit the Fn key on the 8th!

 

[Tori_T]

Edit: Just tried it with 10 cells, I hit the Fn key on the 8th!

I think... if you press Fn again it'll turn off, then press CTRL to turn that on and carry on?

 

[Marc]

I think... if you press Fn again it'll turn off, then press CTRL to turn that on and carry on?

Yeah, I know but I clicked on the next cell before I realised.

 

[Nod]

There's a word I use for situations like that - rhymes with f***!

(I have physically removed the Shift Lock key on all my desktop keyboards.)

Might something like Sugru help if you could build up the ctrl key to make it easier for you to keep it depressed, Marc?

 

[JonathanRyan]

Format paint and then go back and reapply the simple formatting.

Alternatively go into conditional formatting...manage rules and make the conditional formatting rules apply to the cells you want (you can point and click or just edit the ranges) and it will do just the rules and not the other formatting.

 

[steve_lyt]

If you are still having problems , I may be able to help you

 

[Marc]

I did it the long way round n the end, hopefully it's just a one off.