VBA format a cell based on range contents
Home > IT Answers > Development > VBA format a cell based on range contents
VbaNewb13
15 pts.
0
Q:
VBA format a cell based on range contents
VBA, *RANGE, Conditional formatting, Microsoft Excel, Excel VBA, Visual Basic for Applications
I need to create a macro that goes in to a worksheet and looks at the contents of a range to see if it contains any number other than zero. If it does, I need it to turn a separate cell yellow.

'assign variables
Dim rngSat As Range
Dim rngSun As Range
Dim wsOT As Worksheet


Set wsOT = _
Application.Workbooks("Weekend OT.xls").Worksheets("7-8")

Set rngSat = _
Application.Workbooks("Weekend OT.xls").Worksheets("7-8").Range("G37:G40")
Set rngSun = _
Application.Workbooks("Weekend OT.xls").Worksheets("7-8").Range("J37:J40")


If rngSat.Value <> 0 Then
wsOT.Cells("Y5:Y6").Interior.Color = vbYellow
End If
If rngSun.Value <> 0 Then
wsOT.Cells("Y13:Y14").Interior.Color = vbYellow
End If
ASKED: May 14 2009  5:11 AM GMT
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
RELATED QUESTIONS
0
Findsarfaraz
1395 pts.
0
A:
 RATE THIS ANSWER
0
Click to Vote:
  •   0
  •  0
  • AddThis Social Bookmark Button
If I understood correctly you want to format cells yellow where number is zero.
try the code below


Sub ChangeFormat()
Dim c As Range

For Each c In Selection
MsgBox Len(c)
If c.Value = 0 And Len(c) > 0 Then
c.Interior.Color = vbYellow
End If

Next c
End Sub

Regards,
Sarfaraz Ahmed
MS Excel help
Last Answered: May 18 2009  11:34 AM GMT by Findsarfaraz   1395 pts.
  •   
0
0
RSS - Discussions Help
Discuss This Answer:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _



_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

VbaNewb13   15 pts.  |   May 20 2009  6:04AM GMT

Here is what I ended up using

‘See if anyone is working Saturday 2nd Shift, if they are shade support cells yellow
If Application.WorksheetFunction.Sum(rngSat) <> 0 Then
wsOT.Range(”Y5:Y6,Y20,Y26,Y32,AA17,AA21:AA22,AA25,Y45:AA45″).Interior.Color = vbYellow

Else
wsOT.Range(”Y5:Y6,Y20,Y26,Y32,AA17,AA21:AA22,AA25,Y45:AA45″).Interior.Color = vbWhite

End If

‘See if anyone is working Sunday 2nd Shift, if they are shade support cells yellow
If Application.WorksheetFunction.Sum(rngSun) <> 0 Then
wsOT.Range(”Y13:Y14,Y22,Y28,Y34,Y48:AA48″).Interior.Color = vbYellow

Else
wsOT.Range(”Y13:Y14,Y22,Y28,Y34,Y48:AA48″).Interior.Color = vbWhite

End If

‘Change background color to white if info has been provided
For Each Cell In rngComp
If Cell.Text = “” Then
Cell.Interior.Color = vbYellow
ElseIf Cell.Text = “TBD” Then
Cell.Interior.Color = vbYellow
Else
Cell.Interior.Color = vbWhite
End If
Next Cell

 
0