Forum Discussion
Conditional Formatting Formulas
Reference conditional format formula I have in cell F2. Then reference conditional format formula I have in cell F7. Similar formulas but the formula in F7 does not look at as many cells to the right in the spreadsheet. I know how to set up one conditional format formula for all cells in the full range of data in column F. However, I can't figure out how to have one formula for a range of cells in column F and a second formula criteria for another range of cells in column F. I can do it if I utilize format painter and go one cell at a time but I have a rather large file I'm working through so trying to find a quicker way. Also, I'll be sorting the file in numerous ways so the conditional format formula can't be locked in such a way where it works for the initial range when i create the conditional format formulas but it doesn't stay with that specific cell after sorting. I'm probably not explaining my question very well so please inquire for additional info you need to assist me with my question. Thanks,
8 Replies
If you have one conditional formula for the range F2:F6 and another formula for the range F7:F16 and you want to have one conditional formula for the range F2:F16, you may combine those formulas like below and create a single rule for conditional formatting for the range F2:F16.
=IF(ROW()<7,F2<SUM(G2+K2+O2+S2+X2+AC2),F7<SUM(G2+K2+O2))Appreciate the quick response. If I set up the single rule you note below but then sort column F, would the formula still stay with a given cell? Example, cell F2 will have one conditional format formula but F7 will have a different conditional format formula. If I sort my data a different way after I set up the conditional format formulas then the row data that was F2 and F7 may no longer be in those given rows. I need to find a way to keep the conditional format formula with the row data that I originally set it up with. Thanks,
mje131 Let me see if I understand you correctly. Let's say you have the letters A-Z in rows 1-26 and you want to have conditional formatting for rows 1-13 to highlight vowels (A, E, I, O, U) and rows 14-26 to highlight cap letters using 2 strokes/lines (I'm defining them to be -> D, J, L, P, Q, T, V, X). So rows 1, 5, 9 (A, E, I) would be highlighted from 1st rule and rows 16, 17, 20, 22, 24 (P, Q, T, V, X) are highlighted based on 2nd rule. Then you re-sort the list in reverse order Z -> A. The conditional formatting will now highlight letters U, O, L, J, D. If that is what you want then you are all set. If, however, you want A, E, I, P, Q, T, V, X to still be highlighted even though they moved into a different rule range then you need something different.
If the latter is the case, then in order to do that you will need another column (or use an existing column if one exists) that will help define the original range. Having another column that is simply numbered 1, 2, 3, etc.... would work (note that column must be values and can NOT be a formula like =row() since the formula result will change after sorting) and then in Subodh_Tiwari_sktneer formula just reference that column instead of using row(). In my example you could actually use the alphabet itself since it was originally in order from A to Z.
