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?
 

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

Watch MrExcel Video

Forum statistics

Threads
1,123,125
Messages
5,599,850
Members
414,342
Latest member
K Darrell Smith

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