Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Formatting of numbers, but not text, in recurring rows
Situation:
I have a 2007 spreadsheet of repeating groups of 5 distinct rows, ie, numbers in any one of the five represent something different from each of the others tho' the values may be similar. Any one of the five rows may also have random cells with text. I have four "cell value" rules that work as I want -- their ranges and priority effectively allowing them to act across all rows without conflict. Problem: Additionally I want to test for, & CF, Row 1 values < 116, but another row has values that would be affected. What I did: --- New Rule 'Format only cells that contain' 'Cell Value' | 'less than' | 116 --- specified the formatting --- [Still in New Formatting Rule] 'Use a formula...' =MOD(ROW()-9,5)=0 [there are 8 rows of intro above the data] --- OKed my way out. --- This is the last rule evaluated What's right: --- Only Row 1 values < 116 are thus formatted What's wrong: --- All text in Row 1 meets that criterion! Actually, I'm sure it's not paying attention to the "< 116", only to the MOD. I don't want text to be CFd. Can I "AND" in a test for "ISNUMBER"? I can't use ROW with it, because it's the cell level that has to be tested. I tried preceding the problem rule with a rule to test for text, but it's really bogus, =ISTEXT(CELL(0)), and doesn't work. Help, anyone? |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Recurring Numbers | Excel Discussion (Misc queries) | |||
Numbers & text in same cell - conditional formatting | Excel Worksheet Functions | |||
conditional formatting with numbers and text | Excel Worksheet Functions | |||
Denoting recurring numbers in formulas | Excel Worksheet Functions | |||
How do I generate non-recurring random numbers to assign groups? | Excel Discussion (Misc queries) |