Home > IT Answers > Mobile > VBA format a cell based on range contents
VbaNewb13
15 pts.
 VBA format a cell based on range contents
*RANGE, Conditional formatting, Excel VBA, Microsoft 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

Software/Hardware used:
ASKED: May 14, 2009  5:11 AM
UPDATED: May 20, 2009  6:04 AM
RELATED QUESTIONS
Answer Wiki:
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 <a href="http://findsarfaraz.blogspot.com">MS Excel help</a>
Last Wiki Answer Submitted:  May 18, 2009  11:34 am  by  Findsarfaraz   1,395 pts.
All Answer Wiki Contributors:  Findsarfaraz   1,395 pts.
To see all answers submitted to the Answer Wiki: View Answer History.


RSS - Discussions Help
Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

 

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

VbaNewb13
 15 pts.