If active cell value is 1, run this macro
 

Hi There,

Situation - if you select the active cell then click a box, a macro runs
based on the colour in your active cell.
I would like to modify the VBA slightly (see below) to say if the value
of the active cell is 1, then run macro 1x (call "1X"), if cell value is
2, then call Macro 2x, etc.
This is what my existing Macro looks like:

Public Sub Colour_views()

Call unhide_cols

Dim objWS As Excel.Worksheet
Dim objCell As Excel.Range, objR As Excel.Range
Dim i As Byte
Dim J As Long

Set objWS = Sheets("Master")
' Don't forget that wherever you're looking at, the selected cell needs
to have a background
' color that matches the various "views" you've built on the master
sheet.

Set objCell = Application.Selection

J = objCell.Interior.ColorIndex

' Hide columns
' If columns A - E are always going to be there, we only need to start
with Column E, which
' is column number 5.
For i = 6 To 168

Set objR = objWS.Cells(2, i) ' We'll always be looking at row 2 -
the column will change
If objR.Interior.ColorIndex < J Then

objWS.Columns(i).Hidden = True

End If

Next

Set objR = Nothing
Set objCell = Nothing
Set objWS = Nothing

Sheets("master").Select
End Sub


Thanks!!!!!!!

D

*** Sent via Developersdex http://www.developersdex.com ***

If active cell value is 1, run this macro
 

Select Case objCell.Value
Case 1
Call x1
Case 2
Call x2
Case 3
Call x3
End Select

Hints:
1) Subs can't be named "1X", can't have numeric character first. Try "X1"
(or something more descriptive!)
2) Dim i As Long, Byte may work but I why limit yourself? I don't even
bother with Integer anymore (unless required for a Sub or Function arg)
3) Terminology: You don't select the active cell; you select a cell and it
becomes the active cell. :)


"Darin Kramer" wrote:

Hi There,

Situation - if you select the active cell then click a box, a macro runs
based on the colour in your active cell.
I would like to modify the VBA slightly (see below) to say if the value
of the active cell is 1, then run macro 1x (call "1X"), if cell value is
2, then call Macro 2x, etc.
This is what my existing Macro looks like:

Public Sub Colour_views()

Call unhide_cols

Dim objWS As Excel.Worksheet
Dim objCell As Excel.Range, objR As Excel.Range
Dim i As Byte
Dim J As Long

Set objWS = Sheets("Master")
' Don't forget that wherever you're looking at, the selected cell needs
to have a background
' color that matches the various "views" you've built on the master
sheet.

Set objCell = Application.Selection

J = objCell.Interior.ColorIndex

' Hide columns
' If columns A - E are always going to be there, we only need to start
with Column E, which
' is column number 5.
For i = 6 To 168

Set objR = objWS.Cells(2, i) ' We'll always be looking at row 2 -
the column will change
If objR.Interior.ColorIndex < J Then

objWS.Columns(i).Hidden = True

End If

Next

Set objR = Nothing
Set objCell = Nothing
Set objWS = Nothing

Sheets("master").Select
End Sub


Thanks!!!!!!!

D

*** Sent via Developersdex http://www.developersdex.com ***