gengcas
11-26-2010, 12:24 AM
I have 2 question:
1. Is there a way to shorten the code below?
2. How will I insert a command that when you change the value of B2, the macro will automatically run(without me clicking run after I enter a value in B2)?
Public Sub Test()
If Range("B2").Value = Range("C5") Then
Range("C5").EntireColumn.Hidden = False
Else
Range("C5").EntireColumn.Hidden = True
End If
If Range("B2").Value = Range("D5") Then
Range("D5").EntireColumn.Hidden = False
Else
Range("D5").EntireColumn.Hidden = True
End If
If Range("B2").Value = Range("E5") Then
Range("E5").EntireColumn.Hidden = False
Else
Range("E5").EntireColumn.Hidden = True
End If
If Range("B2").Value = Range("F5") Then
Range("F5").EntireColumn.Hidden = False
Else
Range("F5").EntireColumn.Hidden = True
End If
If Range("B2").Value = Range("G5") Then
Range("G5").EntireColumn.Hidden = False
Else
Range("G5").EntireColumn.Hidden = True
End If
If Range("B2").Value = Range("H5") Then
Range("H5").EntireColumn.Hidden = False
Else
Range("H5").EntireColumn.Hidden = True
End If
If Range("B2").Value = Range("I5") Then
Range("I5").EntireColumn.Hidden = False
Else
Range("I5").EntireColumn.Hidden = True
End If
If Range("B2").Value = Range("J5") Then
Range("J5").EntireColumn.Hidden = False
Else
Range("J5").EntireColumn.Hidden = True
End If
If Range("B2").Value = Range("K5") Then
Range("K5").EntireColumn.Hidden = False
Else
Range("K5").EntireColumn.Hidden = True
End If
If Range("B2").Value = Range("L5") Then
Range("L5").EntireColumn.Hidden = False
Else
Range("L5").EntireColumn.Hidden = True
End If
If Range("B2").Value = Range("M5") Then
Range("M5").EntireColumn.Hidden = False
Else
Range("M5").EntireColumn.Hidden = True
End If
If Range("B2").Value = Range("N5") Then
Range("N5").EntireColumn.Hidden = False
Else
Range("N5").EntireColumn.Hidden = True
End If
If Range("B2").Value = Range("O5") Then
Range("O5").EntireColumn.Hidden = False
Else
Range("O5").EntireColumn.Hidden = True
End If
If Range("B2").Value = Range("P5") Then
Range("P5").EntireColumn.Hidden = False
Else
Range("P5").EntireColumn.Hidden = True
End If
End Sub
Thanks!!!
1. Is there a way to shorten the code below?
2. How will I insert a command that when you change the value of B2, the macro will automatically run(without me clicking run after I enter a value in B2)?
Public Sub Test()
If Range("B2").Value = Range("C5") Then
Range("C5").EntireColumn.Hidden = False
Else
Range("C5").EntireColumn.Hidden = True
End If
If Range("B2").Value = Range("D5") Then
Range("D5").EntireColumn.Hidden = False
Else
Range("D5").EntireColumn.Hidden = True
End If
If Range("B2").Value = Range("E5") Then
Range("E5").EntireColumn.Hidden = False
Else
Range("E5").EntireColumn.Hidden = True
End If
If Range("B2").Value = Range("F5") Then
Range("F5").EntireColumn.Hidden = False
Else
Range("F5").EntireColumn.Hidden = True
End If
If Range("B2").Value = Range("G5") Then
Range("G5").EntireColumn.Hidden = False
Else
Range("G5").EntireColumn.Hidden = True
End If
If Range("B2").Value = Range("H5") Then
Range("H5").EntireColumn.Hidden = False
Else
Range("H5").EntireColumn.Hidden = True
End If
If Range("B2").Value = Range("I5") Then
Range("I5").EntireColumn.Hidden = False
Else
Range("I5").EntireColumn.Hidden = True
End If
If Range("B2").Value = Range("J5") Then
Range("J5").EntireColumn.Hidden = False
Else
Range("J5").EntireColumn.Hidden = True
End If
If Range("B2").Value = Range("K5") Then
Range("K5").EntireColumn.Hidden = False
Else
Range("K5").EntireColumn.Hidden = True
End If
If Range("B2").Value = Range("L5") Then
Range("L5").EntireColumn.Hidden = False
Else
Range("L5").EntireColumn.Hidden = True
End If
If Range("B2").Value = Range("M5") Then
Range("M5").EntireColumn.Hidden = False
Else
Range("M5").EntireColumn.Hidden = True
End If
If Range("B2").Value = Range("N5") Then
Range("N5").EntireColumn.Hidden = False
Else
Range("N5").EntireColumn.Hidden = True
End If
If Range("B2").Value = Range("O5") Then
Range("O5").EntireColumn.Hidden = False
Else
Range("O5").EntireColumn.Hidden = True
End If
If Range("B2").Value = Range("P5") Then
Range("P5").EntireColumn.Hidden = False
Else
Range("P5").EntireColumn.Hidden = True
End If
End Sub
Thanks!!!