Copying Conditional formatting with relative cell referencces in the formula desn't work

Carsten2900 , you are welcome.

That's quite old discussion, not sure which exactly formula do you mean. In general conditional formatting rule formula iterates cells in the range, to which the rule is applied, the same way as the same formula in the grid (outside your range) which you is entered against first cell of the range and after that dragged right and down.

SergeiBaklan  I don't get the logic in this rule, but it works fine and solved an issue regarding formatting cells with relative reference! Thanks!

BoWan 

Dear, thanks for taking your time to exactly trace out the errors in the formula.  This formula actually works as planned but the relative reference doesn't change the cell reference by copying down our recipe. I think there is a glitch which we aren't able to catch, due to which this happens. 😉

If at any time you're able to achieve the same with relative reference changing on the given cell, Kindly share the same.

Thanks once again.

SergeiBaklan 

Hi, thanks for your advice, but it does NOT work. I have followed your instructions exactly, but NO new rules are created when I step through all cellls in the range with the mouse button. There is only the original rule, but the "Applied to" is just expanded and refers to the same compared cell as in the first rule.

Morisn 

Hi, Bonjour!

Dear Oscar,

Hope your are doing well,  as I opened my outlook today, read your e-mail concern and quickly started to trial the formula that you have put forward.  As I had similar concern last year and got some help here and some formula just worked, just by changing values here and there.

So here is your formula that you need to modify a little bit to get what you need to do in M.S Excel.

Formula: =NOT(ISBLANK($A2)) Format: Amber Applies to: =$B$2:$F$2,$B$2:$F$300

The difference here is that we are applying formula to cell reference only and format to whole table array.

This formula does the intended work as a result.

Just apply conditional formatting to one of the cell and copy that cell up to ranges you're applying to.

Here is the formula snapshot.

https://paste.pics/IU8M3

Hope this works for you and get the most out of M.S Excel.

Thanks & Regards

Hamad B

Hello there,

Since I don't know if this thread is still alive, I'll be very brief. I am having a similar problem with formatting but what I want to achieve is the following:

Simply, every time that I enter 'Y' on Column A, I want the entire row to change colour. As simple as that.

Actually, I don't even care if it's Y or Nay, as long as that row changes colour when it's not blank. Since I am not allowed to add images in this thread, I am sending the Gyazo link where I captured the examples I want to show.

But first, the formula.

I created a rule first, for row no. 2 as follows:

Formula: =NOT(ISBLANK($A$2)) Format: Amber Applies to: =$B$2:$E$2

When trying to figure out how to replicate, the only way I was able to do it the way I wanted was to duplicate the formula and change its values manually. 

I did that 6 times before I got tired.

The rows I added and modified can be seen here:

https://gyazo.com/2fbd83b871a38ddccba5f01f044362e9

And a sample of the results:

First date row (2) with a 'Y' changes row no. 2 colour to amber:

https://gyazo.com/1c8fce73dee70e8397c6ec161406d6ca

And another one:

https://gyazo.com/23e82b0e0bdd83e82dbde7dceee2edcf

Now, for a few rows, it's doable; however, this is completely impractical and backwards for software such as Excel to be doing this for any number of rows. So there should be a better way to do this.

I have about 300 records to apply this to and may be more.

Hopefully, someone could give me an idea of how to go about this.
Thank you and regards,
Oscar

krozar 

Thank you so much for explaining that you have to remove the second "$" to get the relative value for each cell!!

Matthew500 

You don't need absolute reference on rows in the formula, only on column. With that row will be filled correctly and may correctly apply conditional formatting rules to other rows by format painter or so.

=SEARCH( "previous", $S5)

not $S$5

SergeiBaklan 

I'm trying to copy conditional formatting that colors every single row depending on the value they have in a certain cell. For some reason I must use $$ in the "Rule" section/formula for the row to be completely colored within the row range, otherwise it only colors the first cell of the range.

Do you know how can I setup this so when I copy the conditional formatting the value goes up accordingly to the row I'm copying? The "Applies to" sections bumps up automatically in that case, but the "Rule"  formula section doesn't. 

Thanks!

HB , glad it helped, thank you for the feedback

Thanks SergeiBaklan 

I have read the information through and I find it useful, because I had to also conditional formatting on series of infinite cells.  I tried the formulas mentioned in this conversation and after few trials it worked! 🙂

I don't understand exactly how it works but it does what I want.

Thanks again.

StevanMuller12 

Rule formula could be like =$B2<>$C2 applied to entire range as B2:C999.

With sample file which shows the same data as on screenshot it'll be easier to demonstrate.

Good Day,

We are trying to show the difference in price change of 2 lists of sku and prices.
And original conditional formatting does not have a rule for this help will be much appreciated.

Jbetteridge91435 , glad to help

SergeiBaklanThankyou very much. this has saved me sooooo much time!! your the real MVP!! 

AaronWalker 

Yes, but first one could have side effect in some situations

SergeiBaklan 

Yes, looks like both methods will work provided that the first row of data is selected in the current section.  All good!  Thanks for sharing.

AaronWalker 

Please check in Sheet2 attached

SergeiBaklan 

What you are showing in your reply will not work.  However, the format I presented works for me in my workbook which was attached.  Since I am new to this, I am not sure you see it.  Anyway, good luck.

AaronWalker 

In general

=D11<>C11     Red
=D11=C11     Green

will be more correct

krozar 

Two Rules:  formulas

=D11:D15<>C11:C15     Red

=D11:D15=C11:C15     Green

Applies to: =D11:D15<>C11:C15     same in both rules

varocketry 

Perhaps you may submit sample file removing all extra and sensitive information

SergeiBaklan 

I'll try that.  I am completely unaware the " " is required.  Nor do I understand their operation.

But, I'll try it.  Thanks again.