I have a Data validation where I have row number list from 1-10 and table with data. Now I select data validation and select 5. In this selection result Conditional formatting color row range from 1 to 5. How to do that?
I have a Data validation where I have row number list from 1-10 and table with data. Now I select data validation and select 5. In this selection result Conditional formatting color row range from 1 to 5. How to do that?
Last edited by 7absinth; 06-07-2017 at 08:16 AM.
Ho 7absinth,
Do you have any sample sheet if so attach showing your expected results , make sure you remove sensitive data before uploading.
Punnam
Hi ,
Check this sample sheet .
Punnam
Book1.xlsx
Something like that.
Hi ,
Need more details can you explain .
Now it is clear , how about Raw data can you enter the data in H:M:S format or minutes if possible not using hr in the same cell
Was it possible ?
Last edited by Punnam; 06-07-2017 at 07:04 AM.
So if I select 3 hours Conditional formatting color range from 20(min) to 3 hours. If I select 5 hours Conditional formatting color from 20 (min) to 5 hours. etc. All cells are text. No need for HH:MM:SS formatting.
Last edited by 7absinth; 06-07-2017 at 07:13 AM.
I played with ROW and ROWS formulas but still i found problems. Maybe BETWEEN ROWS somehow to make it? I am really dont know and stuck couple of hours.
For example this Conditional Formatting formula color only one row range but I need all others that are above too:
=MATCH(ROWS($A$5:$A5);$E$2;0) - copied from original workbook
where $A$5:$A5 is sliding dependant range that return rows (rows 1,2,3...)
$E$2 - data validation row # (for example 3)
Last edited by 7absinth; 06-07-2017 at 07:37 AM.
Try this out.
=ROW()<=MATCH($B$1,$A$4:$A$28,0)+ROW($A$3)
used as the CF formula
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
Madness!! Works like a charm! Only this formula is not enough understandable but works. Thank you A LOT Glenn.
=ROW()<=<=MATCH($B$1,$A$4:$A$28,0)+ROW($A$3)
Red: If the row number under consideration <=
Blue: row number of the exact match of cell B1 in cells A4 to A28
Green: plus 3, to take account of the fact that your data start on row 4.
Simple (ish), really,
You're welcome.
If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.
It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.
In excel HOME conditional formation one option is there that you can click and apply conditionals for your range. LIke 1-5 green color, 6-8 - black color and 9-10 red color.
But not in this case. You have a text instead of numbers and each row text is unique.
Only solution is a formula. Secondly need to color specific prior selected row range FROM-TO which rows to color.
Open attached sample that is added in this thread and sooner or later you will notice the problem.
Thanks again to Glenn for solution. This thread marked as solved completely. A++ grade from me.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks