Solved

Excel 2010 Conditional Formatting individual cells based on a different column in same row

Posted on 2014-02-06
14
466 Views
Last Modified: 2014-02-07
Hi, I have a spreadsheet (15 columns displayed) that has a series of columns and then various actual figures that I want to compare to a budgeted figure that will be in an earlier column but on the same row.  

Let's assume figures are in the following:

M8 = Source Qtrly Budget = 200
Y8 = Actual Qtr 1 = 150
AI8 = Actual Qtr 2 = 200
AT8 = Actual Qtr 3 = 225
BE8 = Actual Qtr 4 = 200

In Y8, AI8, AT8, and BE8 I want the highlighting to be as follows:
Green background = Actual for the quarter is exactly as figure in M8
Yellow background = Actual for the quarter is MORE than figure in M8
Red background = Actual for the quarter is LESS than figure in M8

So in the example, I would want Y8 to be Red, AT8 to be yellow, and AI8 & BE8 to be green.  

The issue I have is that I need to have a lot of comparisons so the solution needs to be workable for having nearly 400 rows of data, and 9 "source" comparisons for each row (that are then compared quarterly).  I'm hoping that someone can help with Visual Basic code or something that is workable that Excel won't want to take 5 minutes to crunch through each time something is updated.  

Thank you so much!
0
Comment
Question by:Kevin Buckland
  • 5
  • 5
  • 3
  • +1
14 Comments
 
LVL 49

Expert Comment

by:Rgonzo1971
ID: 39841484
Hi,

Could you send a dummy example?

Regards
0
 
LVL 19

Expert Comment

by:Ken Butters
ID: 39842210
I created a workbook (see attached) that has the conditional rules you specified.

I created 3 rules... one for each color.
Each rule applies to each of the 4 cells that you wanted colored.
EE-1.xlsx
2-7-2014-10-42-53-AM.jpg
0
 

Author Comment

by:Kevin Buckland
ID: 39842468
Hello Ken and Rgonzo1971,

Thank you for your help.  There are additional rows (approximately through row 400, though in reality not every single row will have values).  So I think I need a solution more powerful than Excel's native cell by cell conditional formatting .

I've attached a spreadsheet that gives you a better idea of the scope of what I need.

Thank you again so much for your help.  If anyone is able to figure out a solution it would be SO appreciated.
Q-28358707-Example-Data.xlsx
0
 
LVL 81

Expert Comment

by:byundt
ID: 39842608
I think you could use Conditional Formatting for your purposes, but you need to have the right formulas so they update the relative addresses as you copy down and across while still linking to the right column in M:S.

Select cell Y8, then create the following conditional formatting rules:
=AND(Y8<>"",Y8=INDEX($M8:$S8,MATCH(LEFT(Y$7,13) & "*",$M$7:$S$7,0)))    green
=AND(Y8<>"",Y8>INDEX($M8:$S8,MATCH(LEFT(Y$7,13) & "*",$M$7:$S$7,0)))    yellow
=AND(Y8<>"",Y8<INDEX($M8:$S8,MATCH(LEFT(Y$7,13) & "*",$M$7:$S$7,0)))    red

Next, apply those rules to cells Y8:BK415 by copying Y8 and Paste Special...Formats
Q-28358707-Example-Data.xlsx
0
 
LVL 19

Expert Comment

by:Ken Butters
ID: 39842646
Unless you are against the idea of conditional formatting... (based on title of question, I kind of thought that was where you were going)....

It is very powerful.

attached is sheet with 12 rules... 3 for each of the 4 main columns of data.
Q-28358707-Example-Data.xlsx
0
 

Author Comment

by:Kevin Buckland
ID: 39843140
What if I would like to have have blank rows periodically throughout various rows in the source budget columns but I don't want there to be any formatting when the "actual" columns are blank? So say m8:s8 are all blank - and Y8:AE8 are also all blank - how would I keep Y8:AE8 to not be formatted with green highlighting even though the cell values are equal?

Thank you.
0
 
LVL 81

Expert Comment

by:byundt
ID: 39843175
Kevin Buckland,
Did you try inserting a blank row in the sample workbook I posted?

Had you done so, you would have found that the conditional formatting was not active in the blank row. This is because my formulas tested whether the cell was blank.

Brad
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:Kevin Buckland
ID: 39843262
Brad,

I'm having trouble getting your code into my "real" spreadsheet.  I just don't understand how to decipher it and therefore tweak it to  get it to work in my real spreadsheet.  

Attached is my "real" spreadsheet that I'm trying to get each row in columns Y:AF to look at the budget recommended in columns M:V.

Also, I can put it into another question but what I'd really ideally want it to do is the following:

If the actual amount in Y:AF is exactly the same as corresponding column M:V, shade the background green

If the actual amount in Y:AF is ZERO but there was a budgeted figure >0 in corresponding column M:V, shade the background red

If the actual amount in Y:AF is not equal to but is higher or lower than the corresponding column M:V, shade the background yellow
FY15-MH-Budget-Comparison---Test.xlsx
0
 
LVL 19

Expert Comment

by:Ken Butters
ID: 39843329
I've done the green for you... take a look and see if that is what you are looking for.

I used this conditional...

=AND(NOT(ISBLANK(M5)),NOT(ISBLANK(Y5)),M5=Y5)

so basically in order to color it green, one or the other corresponding cells must have a value... and they have to be equal.

This was applied to the range from Y5 to AG379.

If that is giving you the kind of results you are looking for.. then also need similar but separate rule on same range that would color Yellow or color red.

Instead of M5=Y5 2 new rules would be similar to green rule except change last part of conditional to M5>Y5 or M5<Y5 depending upon red or yellow situation.
FY15-MH-Budget-Comparison---Test.xlsx
0
 
LVL 81

Expert Comment

by:byundt
ID: 39843477
For your new worksheet, I selected cell Y4 and then used the following Conditional Formatting formulas:
=AND(Y4<>"",Y4=INDEX($M4:$V4,MATCH(Y$3,$M$3:$V$3,0)))   green
=AND(Y4=0,INDEX($M4:$V4,MATCH(Y$3,$M$3:$V$3,0))>0)   red
=AND(ABS(Y4)>0,Y4<>INDEX($M4:$V4,MATCH(Y$3,$M$3:$V$3,0)))   yellow

After entering all three formulas, I then used the Conditional Formatting...Manage Rules menu item and changed the effective range to Y4:AF1000.

By way of explanation:
MATCH(Y$3,$M$3:$V$3,0)            Tries to match header label in Y3 to M3:V3. If found returns column index number.
INDEX($M4:$V4,MATCH(Y$3,$M$3:$V$3,0))         Returns value from matching column in M4:V4
Y4=INDEX($M4:$V4,MATCH(Y$3,$M$3:$V$3,0))   Returns TRUE if Y4 equals value in matching column in M4:V4
ABS(Y4)>0           Returns TRUE if Y4 is a non-zero number
FY15-MH-Budget-Comparison---Test.xlsx
0
 

Author Comment

by:Kevin Buckland
ID: 39843518
This is great.  Can one more modification be made whereby if Column F="NO" then there will be no conditional formatting done on the row? If so, can you help me write that?

Thanks again!
0
 
LVL 81

Accepted Solution

by:
byundt earned 500 total points
ID: 39843521
Change the three conditional formatting formulas to:
=AND($F4<>"NO",Y4<>"",Y4=INDEX($M4:$V4,MATCH(Y$3,$M$3:$V$3,0)))   green
=AND($F4<>"NO",Y4=0,INDEX($M4:$V4,MATCH(Y$3,$M$3:$V$3,0))>0)   red
=AND($F4<>"NO",ABS(Y4)>0,Y4<>INDEX($M4:$V4,MATCH(Y$3,$M$3:$V$3,0)))   yellow

Brad
FY15-MH-Budget-Comparison---Test.xlsx
0
 
LVL 81

Expert Comment

by:byundt
ID: 39843529
Kevin Buckland,
The reason I am using INDEX and MATCH to find the right comparison column is in case you want to expand the Conditional Formatting to other fiscal periods, such as in your original question.

If you never plan to do that, then the relative addressing that Ken Butters is suggesting with his formulas is a simpler solution.

Brad
0
 

Author Closing Comment

by:Kevin Buckland
ID: 39843534
Thanks so much!
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Not long ago I saw a question in the VB Script forum that I thought would not take much time. You can read that question (Question ID  (http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_28455246.html)28455246) Here (http…
Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
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…

867 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

12 Experts available now in Live!

Get 1:1 Help Now