• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Copying conditional formatting rule to other columns

rkspeaks

rkspeaks

Member
Hi Hui,


Good morning,


I have a sheet with all rows as countries, columns named as months. I need to copy conditional format formula rule to other columns. The rule in the CF is if the particular months data is above its average, it will be Green. i.e. (B2>average($B$2:$B$30), then format it. But how to copy this to other columns with relative reference with a single shot.


I think I am clear, if you need any more info, get back to me pls.
 
Hi, rkspeaks!

What do you have in column B, to which columns are you going to copy the CF and what do you have in those columns?

Regards!
 
A-Country Code, B-Countries, C to N months. Data is Rain fall for 12 months. I typed the formula in C col, I mean Conditional Format formula i.e. January and want to copy it to till N col. Anymore you want?
 
Hi, rkspeaks!


1>> Select B2, Click Format Painter >> Now select from B3 to B30..

2>> Select B2 > Go to Conditional Formatting >> Manage Rules

In the Applies To box write =$B$2:$B$30

* One more suggestion, If possible change Conditional Formatting in B2 as

Code: 
=$B2>Average($B$2:$B$30)


Please let us know if its working for you..


EDIT:

I just noticed you mentioned "C", so please adjust all "B" to "C"...


Regards,

Deb


@ SirJB7

Some of the member are already completed the Course "How to create Confusion - in 30 Days"..

So I have also joined "Mastering in Blind Shot" Course..


Please don't remind me what happen if customer changes from B2B to B2C, I have a bad experience.. I hope you don't have to face it..
 
Hi, rkspeaks!


I smell something strange...


Column B contains Countries... and CF formula uses the function:

AVERAGE($B$2:$B$30)


Would you please confirm?


Regards!


EDIT: discard this comment, I just read more carefully your second post, I apologize.

EDIT 2: don't discard, I re-read again and the question's still valid.
 
@Debraj Roy

Hi!

I think I should be attending at "How to question *.* /s !?"... Did you notice the country average? I did, but I missed your clarification. And still pending user confirmation.

Regards!

PS: Customer it's always right (if was him who hired me), else "there's the phone of the IVR, option 123, 4573, c, 2nd item".
 
Hi, rkspeaks!


Assuming that you want to highlight the rainy places by months, I should try this:

CF formula: =C2>AVERAGE(C$2:C$30)

Applies to: =$C$2:$N$30


Please confirm if I'm wrong or otherwise just advise if any issue.


Regards!


EDIT: B2B? no, B2C? no, ... it was C:C, but it refused! (Would you believe that I used all "B" just a moment ago?... Should be going to bed...
 
Please wait guys, I will post the excel sheet to avoid the confusion.


https://hotfile.com/dl/168841120/622c07b/Rainfall-US.xlsx.html


I need to copy the formula in the C col to all the months in a single shot. That's the task.


Thank you all.
 
Hi, rkspeaks!


I have to wait 30' for hotfile as I just downloaded another file. I suggest you to proceed as I wrote in my previous post or as this:

- select column C (or C2:C30, which I hope has as CF my formula and not yours), go to Start tab, Clipboard group, Copy Format icon (brush)

- select columns D:N (or D2:N30), and click again on brush.


Please confirm previously what asked in my third upwards post.


Regards!
 
Hi, rkspeak


1>> Change Conditional Formatting in C2 as

Code: 
=C2>AVERAGE(C$2:C$30)

2>> Select C2, Click Format Painter >> Now select from C3 to C30..


or


2>> Select C2 > Go to Conditional Formatting >> Manage Rules

In the Applies To box write =$C$2:$N$30


https://dl.dropbox.com/u/78831150/Excel/copying-conditional-formatting-rule-to-other-columns%20%28rkspeaks%29.xlsx


@ SirJB7,

I am not talking about B:C or B:B, I am talking about B2C & B2B .

I really had a bad experience, and just click on the mind at that time.. ..


Regards,

Deb
 
Hi, rkspeaks!

Glad you solved it. Thanks for your feedback. And welcome back whenever needed or wanted.

Regards!
 
You must log in or register to reply here.
Back
Top