Conditional Formatting - Dragging down

[Bartus]

Hi,


I have been reading the other posts relating to this and been unable to find a solution.


"Using a formula to determine which cell to format"

I have this CF on a cell =Sheet4!D1>1

and want to be able to drag it down to row 2 onwards. With the formula auto changing to =Sheet4!D2>1 etc


I need to apply this CF to 1000+ rows and not to keen to do the CF manually.


Any thoughts?

 

[vijay.vizzu]

Hi Bartus


Can you elaborate your problem in more. Actually i can't able to understand what do you want with Conditional Formatting


1. Assume that you have range (A1:A5000) and you want to apply CF that if A1 value greater than 1 then it should format and like so. If that so then select the range A1:A5000, and apply your formula. It will apply to whole range.


and if above condition is not matching your requirement, then please elaborate more


Regards

Vijay

 

[Bartus]

Hi Vijay,


I need the formula in the CF to change in row.


Ie:

Row 1 - =Sheet4!D1>1

Row 2 - =Sheet4!D2>1

Row 3 - =Sheet4!D3>1


The CF is looking at a Countif result relating to the main sheet.

So if a record is added which matches a previous record the text turns red.


Rgds,

Bartus

 

[NARAYANK991]

Hi Bartus ,


Please note that unlike a normal cell formula , a CF formula will not change with the row ; suppose you wish to apply the CF in the range A1:A3 , which is to be colored RED if the range Sheet4!D1>1 , Sheet4!D2>1 and Sheet4!D3>1.


What you are supposed to do is select the range A1:A3 , and in the CF formula box , enter the formula =Sheet4!D1>1 ; however , Excel does not allow references to other worksheets in a CF formula.


What you need to do is create a named range , call it say Countif_Status , and use the following CF formula :


=INDEX(Countif_Status,Row(A1))>1


Narayan

 

[SirJB7]

Hi, Bartus!


First of all welcome to Chandoo's website Excel forums. Thank you for your joining us and glad to have you here.


As a starting point I'd recommend you to read the green sticky topics at this forums main page. There you'll find general guidelines about how this site and community operates (introducing yourself, posting files, netiquette rules, and so on).


Among them you're prompted to perform searches within this site before posting, because maybe your question had been answered yet.


Feel free to play with different keywords so as to be led thru a wide variety of articles and posts, and if you don't find anything that solves your problem or guides you towards a solution, you'll always be welcome back here. Tell us what you've done, consider uploading a sample file as recommended, and somebody surely will read your post and help you.


And about questions in general...


If you haven't performed yet the search herein, try going to the topmost right zone of this page (Custom Search), type the keywords used in Tags field when creating the topic or other proper words and press Search button. You'd retrieve many links from this website, like the following one(s) -if any posted below-, maybe you find useful information and even the solution. If not please advise so as people who read it could get back to you as soon as possible.


And about this question in particular...


CF changes automatically with the row if you don't specify fixed row references but the formula shown in the CF condition is always referred to the top left cell of the range with that CF.


So in your case you'd simply do this:

a) Place 2, 1, 2 in D1:D3 of worksheet Sheet4.

b) Select A1:F3 of any other worksheet.

c) Go to CF, new rule, select formula and place this formula:

=Sheet4!D1>1

d) Set proper format and accept.


This will show you A1:F1 and A3:F3 cells with the CF applied.


Just advise if any issue.


Regards!