Hi
I would like to find the first value in column C that matches (or is the next highest) based on the value in A1. In the example, cell C8 would be the cell to highlight based on the value in cell A1. I want the highlighting in column C to adjust in accordance with the value in A1. I do not want any other cells to highlight (only the first value above the value in A1).
I have Excel 2007 and not sure of the formulas to use in the conditional formatting.Excel Problem.xlsx
Thanks
Steve
Select C1:C14 and invoke Conditional Formatting| New Rule from the Home tab.. select use a formula to determine which cells to format and apply formula:
then click Format to choose formatting.
Please Login or Register to view this content.
Where there is a will there are many ways.
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Excel Problem.xlsxThanks. Just one question -
Attached is an amended spreadsheet with an additional question.
SH
Last edited by steve 1962; 06-14-2012 at 04:56 AM.
The formula will now be:
=B3=INDEX($B$3:$B$44,MATCH(TRUE,$B$3:$B$44>=$E$1,0))
Thanks very much NBVC.
All working fine thanks to your help.
All the best.
SH
Now that I have amended the worksheet, I have now discovered that one more amendment is required. Are you able to help with one also ?
Thanks
Excel Problem2.xlsx
Select from C3:K44 and re-invoke conditional formatting, change the Applies to range to =$C$3:$K$45 and then select the condition and click Edit and change formula slightly to:
=$K3=INDEX($K$3:$K$45,MATCH(TRUE,$K$3:$K$45>=$M$1,0))
The $ forces all cells to look at column K for conditional check and format.
Select from C3:K44 and re-invoke conditional formatting, change the Applies to range to =$C$3:$K$45 and then select the condition and click Edit and change formula slightly to:
=$K3=INDEX($K$3:$K$45,MATCH(TRUE,$K$3:$K$45>=$M$1,0))
The $ forces all cells to look at column K for conditional check and format.
Hi NBVC
Thanks again. Another success.
I won't bother you again. All is working OK.
All the best.
Steve
Hi NBVC
Thanks again. Another success.
I won't bother you again. All is working OK.
All the best.
Steve
Hi NBVC
The spreadsheet is working well here at home. Just an observation that I have made at work (the file is opened on another computer). My computer at work is running Windows XP and Excel 2007.
When I open the file, the conditional formatting does not work. However, when I go into CONDITIONAL FORMATTING / MANAGE RULES / EDIT RULE, then I go into the box (Format values where this formula is true: and press F2 to edit the formula (I don't even change the formula in any way) and then exit edit and then CANCEL out of the Edit Formatting all together, the conditional formatting then works. It's as if something is reset on my work computer after going into edit mode. I have to do this each time I close and then reopen the file. This is something that I have not experienced before. Very weird.
Thanks
SH
It happens with me too when I re-open your file....
Try changing formula to:
=$K3=INDEX($K$3:$K$45,MATCH(TRUE,INDEX($K$3:$K$45>=$M$1,0),0))
I think there is some sort of but in 2007 and conditional formatting with array formulas. The above seems to work.
Thanks again NBVC
I have changed the formula at work and all is well.
All the best.
Steve
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks