Forum Discussion
Conditional Formatting Formulas
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.
Appreciate the quick reply. Yes, I think you are understanding my question based on your response. I believe the later is the case per you reply and is exactly what I'm looking for. Please reference attached. It shows the formula I typed into my file along with the error message I'm receiving. My guess is that it's something simple in my formula that I need to update but can't figure it out. Any additional help would be appreciated. Thanks,
Just noticed a typo in the suggested formula.
The correct formula should be this...
=IF(ROW()<7,F2<SUM(G2+K2+O2+S2+X2+AC2),F2<SUM(G2+K2+O2))Yes, if you sort the data, the conditional formatting should work properly.
