Solved

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

Posted on 2014-02-06
14
457 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 48

Expert Comment

by:Rgonzo1971
Comment Utility
Hi,

Could you send a dummy example?

Regards
0
 
LVL 19

Expert Comment

by:Ken Butters
Comment Utility
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
Comment Utility
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 80

Expert Comment

by:byundt
Comment Utility
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
Comment Utility
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
Comment Utility
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 80

Expert Comment

by:byundt
Comment Utility
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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 

Author Comment

by:Kevin Buckland
Comment Utility
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
Comment Utility
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 80

Expert Comment

by:byundt
Comment Utility
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
Comment Utility
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 80

Accepted Solution

by:
byundt earned 500 total points
Comment Utility
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 80

Expert Comment

by:byundt
Comment Utility
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
Comment Utility
Thanks so much!
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

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…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

772 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

16 Experts available now in Live!

Get 1:1 Help Now