Solved

Conditional formatting

Posted on 2012-03-28
13
189 Views
Last Modified: 2012-03-29
Hi,
I am looking for some help in applying a conditional format to a number of cells in a number of different rows.
The criteria is as follows;
If the current cell value is greater than 0,
AND
the value of the cell in the same row, but column C is greater than $A$1

I have tried a number of things but it keeps reverting either to a text line in the condition, or giving an error.

I am at home at the moment, and do not have Excel on this laptop.
I will check in again from work tomorrow.

Thanks in advance.
0
Comment
Question by:Stephen Byrom
  • 4
  • 4
  • 3
  • +1
13 Comments
 
LVL 33

Expert Comment

by:Norie
ID: 37778856
Which rows/cells do you want to apply the formatting to?
0
 
LVL 5

Expert Comment

by:Apothis
ID: 37778877
Give this a try:

Conditional Formatting > New Rule . Use a formula to determine which cells to format:
=D2>0
=$C2>$A$1
Then use the format painter to copy the conditional formatting.

Good luck!
0
 
LVL 9

Expert Comment

by:OCDan
ID: 37778896
Assuming the cell in question is in column B this works perfectly using a single formula:
=AND($B1>0,$C1>$A$1)
0
 
LVL 5

Expert Comment

by:Apothis
ID: 37778925
I messed up a little on mine. OCDan had a better approach, but to be able to apply the formatting to different columns, you'd want to take off the first $:
=AND(E2>0,$C2>$A$1)
0
 
LVL 9

Expert Comment

by:OCDan
ID: 37778934
Nice amendment Apothis, makes it a lot easier to use on a number of columns.
0
 
LVL 5

Expert Comment

by:Apothis
ID: 37778978
Thanks, the AND function was far better than applying 2 rule in my approach, then I forgot to add to stop if true, and I had my > should have been <=. So what I was trying to accomplish:

=D2<=0          (apply no formatting changes) (stop if true)
=$C2>$A$1   (your desired formatting)
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 33

Expert Comment

by:Norie
ID: 37778998
Did I miss something?

Where did column D come into this?
0
 
LVL 5

Expert Comment

by:Apothis
ID: 37779028
D, or B, or E was the 'current cell', as we weren't sure where you wanted to apply the formatting.
0
 
LVL 1

Author Comment

by:Stephen Byrom
ID: 37783489
Hi,
Thanks to both for the advice.
I have tried a couple of ways to get it to work, to no avail.
I have attached a copy of the sheet in question so that you can see what I am after.
The numbers in columns G, J, M.....etc. etc. need to change to something noticeable (maybe yellow fill red font) if, (the number is >0 AND the date in column C of the same row is greater than the value of A1.)  We need to be able to notice quickly if we have more than 10 day's stock of any product item, which is why the formula in A1 is "Today()+10"

I update this sheet on a weekly basis and copy/insert copied rows every two weeks, so I would like the conditional formatting to copy down.

I managed to get a simple condition to work with less than 0, but the compound condition and relative to column C of the same row, has me baffled.
Thanks for your time.
Book1.xlsx
0
 
LVL 33

Expert Comment

by:Norie
ID: 37783566
Which cells do you actually want to format?

In the attachment you seem to be formatting columns B:AQ, not just columns G, J and M.
0
 
LVL 1

Author Comment

by:Stephen Byrom
ID: 37783600
Hi,
I have a number of formats going on. But what I need is the numbers in columns G, J, M.....etc. (stock on hand) to change format when the stock on hand is greater than zero AND the date in column C of the same row is greater than the value of A1
Thanks
0
 
LVL 33

Accepted Solution

by:
Norie earned 500 total points
ID: 37783853
Select E4:AQ84, goto conditional formatting, enter this formula and format as required.

=AND(E4>0, $C4>$A$1,COLUMN()>COLUMN($D$1),COLUMN()<COLUMN($AR1),MOD(COLUMN(),3)=1)

This checks the value in the cell, checks the date in column C of the row against A1 and checks if the cell is in columns E-AQ and if the column is the 3rd.

If all are checks are true it returns TRUE.
0
 
LVL 1

Author Closing Comment

by:Stephen Byrom
ID: 37783978
Brilliant!!
Thanks so much for your time!
Looking at the complexity of the formula, I know I would have never worked that out.
Thanks again.
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now