Highlight/Fill Multiple cells based on one cells value.

[cbc818]

Hello Everyone,

Basically I want to show how many people will be needed per station bassed on Cell O16's value. For example if O16 read 8, I would want cells D and E 5-12 to be highlighted. I'm sure this is a simple formula but I'm a neophyte at this.

Thank you!

 

[offthelip]

try this in the worksheet change event:
Obviously you can choose what formatting you waant to show

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Cells(16, 15)) Is Nothing Then Exit Sub
  If IsNumeric(Target) Then
 Application.EnableEvents = False
       Lastrow = Cells(Rows.Count, "D").End(xlUp).Row
       With Range(Cells(5, 4), Cells(Lastrow, 5)).Interior
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
       End With
       
       With Range(Cells(5, 4), Cells(5 + Target, 5)).Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
 Application.EnableEvents = True
  End If
End Sub

 

[Peter_SSs]

This can be done with Excel's built-in Conditional Formatting. The steps would be ..

1. Select from D5 down to E?? (whatever row is at least as many as you would ever want highlighted). In my example below, I selected D5:E30.
2. Home -> Condition Formatting -> New Rule ... -> Use a formula to determine which cells to format -> Format values where this formula is true: =ROWS($D$5:$D5)<=$O$16 -> Format... -> Fill tab -> Choose colour -> OK -> OK

Excel Workbook
	D	E	F	O
4
5
6
7
8
9
10
11
12
13
14
15
16				8
17
CF

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D5	1. / Formula is =ROWS($D$5:$D5)<=$O$16	Abc