Conditional formatting question (Excel xp)

Topic

New Reply

I have one sheet in a workbook that has some formulas and then conditionally formats cells based on the results of the formula. I then wanted to paste the cells to another sheet along with those formats. For some reason i can paste the values but cannot get the formats to come with it. I am adding font bolding, italic and borders but they won’t carry over to the 2nd sheet even if i paste special, and click on formats. What am i doing wrong here? Thank you.

Reply | Quote

Replies

Are you trying to paste regular formatting or conditional formatting? If you’re pasting conditional formatting, you might want to check the cell references in your conditional formats after you’ve pasted them. They may not be referring to the cells you expect.

Reply | Quote

hmmm, i see what u mean. I’ve attached a test. How do i copy A3:N3 from the stat sheet to the data sheet along with the borders and the italic and bold formatting? thanks

Reply | Quote

The formatting is being copied across, look at the formatting via Format Cells / Font and Borders, it’s the same on both sheets.

The conditional formatting can also be copied across using Paste, but then the CF on your data sheet refers to the cells on your data sheet, which do not fulfil the conditions. You will have to change the CF formulas to refer to your stat sheet to get it to work.

Reply | Quote

i thought my CF formulas were referring to my stat sheet. I guess i don’t understand.

Reply | Quote

The formula for CF of H3 on your stat sheet refers to cells H1, H3, and H8 also on your stat sheet. The values in these cells fulfil the conditions, so H3 is formatted accordingly.

The formula for CF of H3 on your data sheet refers to cells H1, H3 and H8 on your data sheet, H1 and H8 are blank on your data sheet so the conditions are not met and therefore H3 is not formatted the way you want. If you copy cells H1 and H8 from your stat sheet to H1 and H8 in your data sheet you will find H3 on your data sheet does become formatted the way you want.

To do what you want, that is to capture the format of H3 and paste it somewhere else, effectively turning it from conditional formatting to ‘straight’ formatting is much more difficult, and I cannot see a way of doing it, though possibly via VBA…

Reply | Quote

ok, thank you very much for the help.

Reply | Quote

ok, thank you very much for the help.

Reply | Quote

The formula for CF of H3 on your stat sheet refers to cells H1, H3, and H8 also on your stat sheet. The values in these cells fulfil the conditions, so H3 is formatted accordingly.

The formula for CF of H3 on your data sheet refers to cells H1, H3 and H8 on your data sheet, H1 and H8 are blank on your data sheet so the conditions are not met and therefore H3 is not formatted the way you want. If you copy cells H1 and H8 from your stat sheet to H1 and H8 in your data sheet you will find H3 on your data sheet does become formatted the way you want.

To do what you want, that is to capture the format of H3 and paste it somewhere else, effectively turning it from conditional formatting to ‘straight’ formatting is much more difficult, and I cannot see a way of doing it, though possibly via VBA…

Reply | Quote

OOps! sorry I did not see that H8 on your stat sheet is not a simple value, if you Paste Special / Values from H8 stat sheet to H8 data sheet, H3 data sheet becomes CF’d as you want.

Also I found that conditional formats cannot refer to other worksheets or workbooks, therefore you cannot make the CF of H3 on the data sheet depend on H1 and H8 on the stat sheet (which would be messy anyway).

This may need the attention of a VB Expert.

Reply | Quote

You can use values on other sheets if you NAME them first (insert name define) and then refer to the name.

Naming ranges is a “good trick” to get around many of the “requirements” for being on the same sheet. Named ranges in many instances (not all) will make excel act like they are on the same sheet.

Steve

Reply | Quote

Very good point Steve.

Reply | Quote

Very good point Steve.

Reply | Quote

You can use values on other sheets if you NAME them first (insert name define) and then refer to the name.

Naming ranges is a “good trick” to get around many of the “requirements” for being on the same sheet. Named ranges in many instances (not all) will make excel act like they are on the same sheet.

Steve

Reply | Quote

OOps! sorry I did not see that H8 on your stat sheet is not a simple value, if you Paste Special / Values from H8 stat sheet to H8 data sheet, H3 data sheet becomes CF’d as you want.

Also I found that conditional formats cannot refer to other worksheets or workbooks, therefore you cannot make the CF of H3 on the data sheet depend on H1 and H8 on the stat sheet (which would be messy anyway).

This may need the attention of a VB Expert.

Reply | Quote

If you name the ranges (insert- name -define)
Name: CompRow
Refersto: =stat!$1:$1

and
Name: SigRow
Refersto: =stat!$8:$8

You can set the conditionl formatting formulas on data to:
Condition 1:
=AND(INDEX(SigRow,COLUMN(D8))=”Sig”,D3>=INDEX(CompRow,COLUMN(D3)))

Condition 2:
=AND(INDEX(SigRow,COLUMN(D8))=”Sig”,D3<INDEX(CompRow,COLUMN(D3)))

And they will refer to the ranges in Stats

Steve

Reply | Quote

If you name the ranges (insert- name -define)
Name: CompRow
Refersto: =stat!$1:$1

and
Name: SigRow
Refersto: =stat!$8:$8

You can set the conditionl formatting formulas on data to:
Condition 1:
=AND(INDEX(SigRow,COLUMN(D8))=”Sig”,D3>=INDEX(CompRow,COLUMN(D3)))

Condition 2:
=AND(INDEX(SigRow,COLUMN(D8))=”Sig”,D3<INDEX(CompRow,COLUMN(D3)))

And they will refer to the ranges in Stats

Steve

Reply | Quote

i thought my CF formulas were referring to my stat sheet. I guess i don’t understand.

Reply | Quote

The formatting is being copied across, look at the formatting via Format Cells / Font and Borders, it’s the same on both sheets.

The conditional formatting can also be copied across using Paste, but then the CF on your data sheet refers to the cells on your data sheet, which do not fulfil the conditions. You will have to change the CF formulas to refer to your stat sheet to get it to work.

Reply | Quote

hmmm, i see what u mean. I’ve attached a test. How do i copy A3:N3 from the stat sheet to the data sheet along with the borders and the italic and bold formatting? thanks

Reply | Quote

Are you trying to paste regular formatting or conditional formatting? If you’re pasting conditional formatting, you might want to check the cell references in your conditional formats after you’ve pasted them. They may not be referring to the cells you expect.

Reply | Quote