How to get a 2nd drop down box to update the range (Not just use C6:C7)based on selection in 1st drop down box.
Example:
If Fat, Use range from C6:C7
If Skinny, Use range from D6:D7
You can direct put the IF statement in 2nd drop down validation, like
=IF(Sheet1!$A$1="Fat", Sheet1!$C$6:$C$7, Sheet1!$D$6:$D$7)
Excel provides conditional formatting which allows you to change the color and other attributes of a cell based on the content of the cell. There is no way, unfortunately, to easily hide rows based on the value of a particular cell in a row. You can, however, achieve the same effect by using a macro to analyze the cell and adjust row height accordingly. The following macro will examine a particular cell in the first 100 rows of a worksheet, and then hide the row if the value in the cell is less than 5.
Sub HideRows()
BeginRow = 1
EndRow = 100
ChkCol = 3
For RowCnt = BeginRow To EndRow
If Cells(RowCnt, ChkCol).Value < 5 Then
Cells(RowCnt, ChkCol).EntireRow.Hidden = True
End If
Next RowCnt
End Sub
You can modify the macro so that it checks a different beginning row, ending row, and column by simply changing the first three variables set in the macro. You can also easily change the value that is checked for within the For … Next loop.
You should note that this macro doesn’t unhide any rows, it simply hides them. If you are checking the contents of a cell that can change, you may want to modify the macro a bit so that it will either hide or unhide a row, as necessary. The following variation will do the trick:
Sub HURows()
BeginRow = 1
EndRow = 100
ChkCol = 3
For RowCnt = BeginRow To EndRow
If Cells(RowCnt, ChkCol).Value < 5 Then
Cells(RowCnt, ChkCol).EntireRow.Hidden = True
Else
Cells(RowCnt, ChkCol).EntireRow.Hidden = False
End If
Next RowCnt
End Sub
Recent Comments