Simple VBA to Color cell and Hide preeceding cells

[alishabbir]

Hello,

I am very new VBA user - like 1 week old and need some basic help in customizing the model I have built. The first tab (cover page) gives the user to select a date from drop down. On selecting Oct, FY17, I would like to achieve the following:

1. User selects Oct, FY17 from Cover page (this is a dynamic cell = "D7")
2. Macro goes on Sheet3 and looks for text as in "D7" in Row 9 cells F:P and changes the color of the cell which contains the same text as =D7 and the all cells preceding it in the range.
Ex: Cells F:P have Jul FY17, Aug FY17, Sep FY17, Oct FY17, Nov FY17..... So the macro would color the cell that has "Oct FY17" and the months before it.

3. Macro goes on Sheet3 and looks for text as in "D7" in Row 9 cells S:AD and HIDES cell which contains the same text as =D7 and the all cells preceding it in the range.
Ex: Cells S:AD have Jul FY17, Aug FY17, Sep FY17, Oct FY17, Nov FY17..... So the macro would HIDE the cell that has "Oct FY17" and the months before it.

The Macro runs on Sheet3 only. Now, if it does not hide or color the preceeding cells then it is fine as long as it hides and colors the right columns.

Thank you in advance.

 

[JoeMo]

Hello,

I am very new VBA user - like 1 week old and need some basic help in customizing the model I have built. The first tab (cover page) gives the user to select a date from drop down. On selecting Oct, FY17, I would like to achieve the following:

1. User selects Oct, FY17 from Cover page (this is a dynamic cell = "D7")
2. Macro goes on Sheet3 and looks for text as in "D7" in Row 9 cells F:P and changes the color of the cell which contains the same text as =D7 and the all cells preceding it in the range.
Ex: Cells F:P have Jul FY17, Aug FY17, Sep FY17, Oct FY17, Nov FY17..... So the macro would color the cell that has "Oct FY17" and the months before it.

3. Macro goes on Sheet3 and looks for text as in "D7" in Row 9 cells S:AD and HIDES cell which contains the same text as =D7 and the all cells preceding it in the range.
Ex: Cells S:AD have Jul FY17, Aug FY17, Sep FY17, Oct FY17, Nov FY17..... So the macro would HIDE the cell that has "Oct FY17" and the months before it.

The Macro runs on Sheet3 only. Now, if it does not hide or color the preceeding cells then it is fine as long as it hides and colors the right columns.

Thank you in advance.

Welcome to the forum!

Individual cells cannot be hidden. To hide a cell requires hiding either the entire row or entire column the cell is in. If that's what you want, can you specify whether you want to hide the row or the column?