Conditional Formatting - Formula not changing across the row

[amandachar15]

Hello!
I am using conditional formatting that is dependent on either a cell in a different sheet (same workbook). Here are some examples:

=OR('Configuration Name(s)'!$D6="",'Configuration Name(s)'!$D6="No")

='Configuration Name(s)'!$D6="Yes"

I have 26 columns I need to copy these across in the same row. I want the reference column to remain as D, but I want the row number to change. I have tried copying and pasting the formatting, the row number will not change.

Any ideas for copying this so that I don't have to go into the individual cells in each row I want this to copy across? The same thing happens a few times within the sheet and will mean more than 100 individual edits.

Thanks!

 

[RickXL]

Hi and welcome to the MrExcel Message Board.

Conditional Formatting is good but you need to watch it like a hawk. I can generally beat it into submission but it has a way of fighting back and every time you fix something it breaks something else.

Your formulas look good but I struggled to get the sheet name into the formula. It kept complaining about using another workbook.

You need to select the whole range to be affected by the conditional format prior to entering the conditional formatting dialog.
When in the Manage Rules item of the menu, check that the ranges specified on the Conditional Formatting Rules manager screen are correct in all lines. If those ranges are correct then the formula you entered should be effectively filled down and across into that range. If the rules are not correct you can amend them on that screen.

I also always select This Worksheet in the "Show formatting rules for:" box at the top of that dialog. That will show you if you have more rules than you thought.

I hope this helps.

 

[Joe4]

have 26 columns I need to copy these across in the same row. I want the reference column to remain as D, but I want the row number to change. I have tried copying and pasting the formatting, the row number will not change.

Are you saying that you want the row number to change in your formula, while you are copying Conditional Formatting formula to other columns in the same row, or different rows?

If applying/copying the Conditional Formatting formula to other columns within the SAME row, I would not expect those row reference numbers to change (at least not how you have written them - you would need a dynamic formula to come up with the row numbers you want).

If you are talking about applying the Conditional Formatting formulas to other rows, they should increment. However, note that viewing the Conditional Formatting rules can be a little misleading. When applied to a large range, I often see that it just shows you the formula as applied to the first range (even though it is applying it correctly to the other ranges).

Also note. I do not like using the Format Painter to copy Conditional Formatting. If I want to apply Conditional Formatting to a multi-cell range, I will highlight the entire range, and the write the Conditional Formatting formula as it applies to the first cell in your highlighted range. As long as you have applied the absolute and relative cell reference symbols correctly, Excel will automatically adjust the formula for the other cells in your range (even though the formula may look a little funny when you look at it).

 

[amandachar15]

Hi, Joe4,
I want the row number to change while copying Conditional Formatting formula to other columns in the SAME row. Do you have any ideas for a dynamic formula to start with to get this? Before last week I stuck to Vlookups and Sums! I saw one nesting Address and Column in indirect, but I couldn't get it to work.

Thanks for your reply!

(PS. I don't like format painter either. I have to copy and then paste the formatting into each cell and then change my row number)

Are you saying that you want the row number to change in your formula, while you are copying Conditional Formatting formula to other columns in the same row, or different rows?

If applying/copying the Conditional Formatting formula to other columns within the SAME row, I would not expect those row reference numbers to change (at least not how you have written them - you would need a dynamic formula to come up with the row numbers you want).

If you are talking about applying the Conditional Formatting formulas to other rows, they should increment. However, note that viewing the Conditional Formatting rules can be a little misleading. When applied to a large range, I often see that it just shows you the formula as applied to the first range (even though it is applying it correctly to the other ranges).

Also note. I do not like using the Format Painter to copy Conditional Formatting. If I want to apply Conditional Formatting to a multi-cell range, I will highlight the entire range, and the write the Conditional Formatting formula as it applies to the first cell in your highlighted range. As long as you have applied the absolute and relative cell reference symbols correctly, Excel will automatically adjust the formula for the other cells in your range (even though the formula may look a little funny when you look at it).

 

[Joe4]

I want the row number to change while copying Conditional Formatting formula to other columns in the SAME row. Do you have any ideas for a dynamic formula to start with to get this?

Please provide specific examples of your formulas, what columns they are going in, and exactly how the row numbers are increasing.

 

[amandachar15]

Please provide specific examples of your formulas, what columns they are going in, and exactly how the row numbers are increasing.

Sure, I have two worksheets: Configuration Name(s) and Youth Tab

On Configuration Names worksheet, in Column C, rows 5 through 19, I have a yes or no answer

On Youth Tab worksheet in Row 32, Columns C I have the following conditional formatting formula: ='Configuration Name(s)'!$C5="No" which turns the cell in C32 gray

I want to copy this formula across Row 32 to apply to columns C through Q, each column should correspond to a different row in column C of the Configuration Name(s) worksheet. For example, the formula in Youth Tab Row 32, column D should be: ='Configuration Name(s)'!$C6="No" and in Column E should be: ='Configuration Name(s)'!$C7="No", etc

THanks for taking the time to help me see if there is a solution! I really appreciate it

 

[Joe4]

Hmmm...

Which version of Excel are you using?
I am using Excel 2007, and it appears that is not possible on my version. I get the message:
"You cannot use references to other worksheets or workbooks for Conditional Formatting criteria."

 

[Joe4]

If you are on a version of Excel that does allow you to use other Worksheets in Conditional Formatting, this would be the Conditoional Formatting formula that you could use for cells C32:Q32:

=INDIRECT("'Configuration Names'!C" & COLUMN()+2)="No"

 

[RickXL]

"You cannot use references to other worksheets or workbooks for Conditional Formatting criteria."

It says the same to me with Excel 2013. However, if I select the start cell then amend the formula rather than just entering the formula firectly into the box then it seems to work. A slight bug-ette, possibly ... ?

 

[amandachar15]

Thanks! I will try that little formula. If that doesn't work, I will just see how I can amend the layout of the reference sheet differently. I am using Excel 2016. Thanks for all of your help, I really appreciate the replies, feedback and ideas.