Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2014-02-06
14
Medium Priority
?
483 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
  • 3
  • +1
14 Comments
 
LVL 52

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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 

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 2000 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

715 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