Currently, with the function below, user clicks a button in Sheets(2) and within the same workbook, user selects a cell or a range of cells and those cells are shaded based upon a format. Okay, fine so this works. How can I run this accross multiple workbooks though?
Lets say I have a 'control' workbook. I click the 'Run Macro' button, and the following sequence occurs:
> User selects workbook (easy step)
> Workbook opens and user selects a range in the workbook
> Shades all the cells in the region
> Save the workbook and closes
> Prompts the user to select another workbook
> Workbook opens and user selects a range.... (and keep looping till user doesn't want to select a WB).
Here is the code I used to shade cells:
Public Sub Color_Range()
Dim cell_count As Integer
Dim mycell As Range
Set mycell = Application.InputBox(prompt:="Select a cell or table to shade. If you select just 1" _
& "cell, this macro will determine the table to shade", Type:=8)
cell_count = 0
For Each cell In mycell
cell_count = cell_count + 1
If cell_count = 1 Then
Set_Range_States mycell, last_row, last_column, table_range
Note: Shade_Cells and Set_Range_States are custom built.