Cond Formatting Problem

[Crakkers]

Hi
I am trialing a teachers markbook that compares teacher grades to target grades. it colour codes red, amber and green the grades column relative to the target grade. BUT, when I copy and paste to increade the number of teacher grades columns the colour format loses its rules and does not copy.

Advice or solution sought please.

Thanks

Crakkers

can you attach a file here?

 

[Fishboy]

Hi Crakkers, welcome to the boards.

Once you have copy / pasted, select the last row with known working formatting, then from the Home tab double-click on the format painter. This will then allow you to "paint" the formatting from the selected range to wherever you like.

Alternatively, when pasting the copied columns, right-click, paste special and select "Keep source formatting".

Hopefully one of these should help you out.

 

[Crakkers]

Hi Crakkers, welcome to the boards.

Once you have copy / pasted, select the last row with known working formatting, then from the Home tab double-click on the format painter. This will then allow you to "paint" the formatting from the selected range to wherever you like.

Alternatively, when pasting the copied columns, right-click, paste special and select "Keep source formatting".

Hopefully one of these should help you out.

Hi, No sorry, either this does not work, or i cant follow your instructions. Is it possible to attach my file? I cant see an attach function here!?

Crakkers

 

[Fishboy]

Hi again Crakkers,

Sorry, you cannot post files directly to the forums. You can host them somewhere like dropbox or onedrive and share the link, but that is the closest you will get.

In the meantime, lets do some basic troubleshooting that I perhaps should have got out of the way at the start...

What version of Excel are you using?

Are you able to go into the existing conditional formatting and copy one of the rules out and post it here? If we can see the code being used we might be able to spot something that would cause it to break.

 

[pedro-egoli]

*******s
Check out these adddins recommended by administrators
Download

http://www.mrexcel.com/forum/board-announcements/515787-forum-posting-guidelines.html#post2545970

Pedro

 

[Crakkers]

Hi again Crakkers,

Sorry, you cannot post files directly to the forums. You can host them somewhere like dropbox or onedrive and share the link, but that is the closest you will get.

In the meantime, lets do some basic troubleshooting that I perhaps should have got out of the way at the start...

What version of Excel are you using?

Are you able to go into the existing conditional formatting and copy one of the rules out and post it here? If we can see the code being used we might be able to spot something that would cause it to break.

Hi

Its 2007.

The referencing of grades to target is doe via; =MATCH($C3,Grade,0)-MATCH($B3,Grade,0)

CF is as: =MATCH($C3,Grade,0)-MATCH($B3,Grade,0)<=0

So when I click and drag to expand the input grade column which is column C in this example and the target grade is in column B the CF colour scheme is lost. So if I click and drag to copy across to colum F the code reamis as =MATCH($C3,Grade,0)-MATCH($B3,Grade,0)<=0, where as it should be comparing F to B. What I dont want to do is edit each column in turn.

Thanks

Crakkers

 

[Fishboy]

Hi

Its 2007.

The referencing of grades to target is doe via; =MATCH($C3,Grade,0)-MATCH($B3,Grade,0)

CF is as: =MATCH($C3,Grade,0)-MATCH($B3,Grade,0)<=0

So when I click and drag to expand the input grade column which is column C in this example and the target grade is in column B the CF colour scheme is lost. So if I click and drag to copy across to colum F the code reamis as =MATCH($C3,Grade,0)-MATCH($B3,Grade,0)<=0, where as it should be comparing F to B. What I dont want to do is edit each column in turn.

Thanks

Crakkers

Hi again Crakkers,

The $ before the column letters in your examples above are explicitly telling Excel to use those columns. These values are "locked in" and do not change when dragged or copied around the sheet. If you need these values to increment intelligently as you drag / copy, you need to remove the preceding $ from any columns you are expecting to change relatively when you move the formula around. In theory

=MATCH($C3,Grade,0)-MATCH($B3,Grade,0)<=0

Would become

=MATCH(C3,Grade,0)-MATCH($B3,Grade,0)<=0

Note that as you always want to compare to column B regardless, the $ preceding B3 stays. This tells Excel that the B part is locked and should not be changed.
When $C3 becomes C3, Excel no longer tries to lock the C value and should allow it to increment relatively to a new column when the formula is moved.

 

[Crakkers]

hello,

thanks, but sorry, that does not solve the formatting issues.

Crakkers