Hiding rows based on cell values

awmperry

New Member
Joined
Mar 13, 2011
Messages
4
I have a spreadsheet with a number of characteristics listed (column E), each with a set level at which they become available (with the level shown in column B) and a points cost (column G). Some require other characteristics as prerequisites (column I), others are duplicated elsewhere on the sheet (for instance, the characteristics in E208 and E251), and others may be selected several times (times available listed in column F, with the number of times selected in column J). There's a drop-down list in cell E2 listing (levels) 1-8. Characteristics are selected by entering a "1" (or, for multiply-selectable characteristics, another number) in column J.

Ideally, when I refer to formatting (greying out etc) I'd like it to only apply to the parts within the table (columns B-K) rather than the whole row. But that's just wallpaper.

What I would like it to do is fourfold:

  • Hide all rows with levels above the level selected in E2 (for instance, "Logic" in E67 is level 2 (C67 = "2"); if E2=1, it should be hidden, and if E2>=2 it should be visible).
  • Grey out the background in rows that don't meet the prerequisites (for instance, grey out row 121 if the sum of cells J85, J208 and J251 (the prerequisite characteristic, in this case available in three places) = 0)
  • With duplicates, grey out the background in all but the cheapest; in that example, the costs in G85 (400), G208 (400) and G251 (200) should be compared, and rows 85 and 208 greyed out. If the one in row 251 wasn't available until level 4, for instance, the other two should remain normal until level 4 is selected to be made visible.
  • Grey out the text of rows where the cost in column G is greater than the balance remaining (listed in K1).
I think that's about it. It's a lengthy wishlist, I know, and I'm rather a novice with VB - any advice?
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,167,991
Messages
5,856,689
Members
431,828
Latest member
kARTIK12345

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top