Changing cell colour in Range

[viewmaster]

Hi all!

I got some help with this bit of code, but i wanted to modify it a
little. I haven't had any luck. I was hoping to check the values of a
couple of cells rather than just "G14:S14" (eg: G13:S13...G17:S17) This
bit of code works, however i'm not sure how to get it to read each of
the rows. The conditions are the same for each row. Any help would be
greatly appreciated!! Thanks


Private Sub Worksheet_Change(ByVal Target As Range)
Dim bOk as Boolean, cell as Range
Dim icolor As Integer
If Not Intersect(Target, Range("G14:S14")) Is Nothing Then
bOk = True

for each cell in Range("G14:S14")
if not isdate(cell.Value) then
if cell.Text <> "N/A" then
bOK = False
exit for
end if
end if
Next
If bOk then
Range("F14").Interior.ColorIndex = 4
Else
Range("F14").Interior.ColorIndex = 0
End If
End if

End Sub

 

[Tom Ogilvy]

Private Sub Worksheet_Change(ByVal Target As Range)
Dim bOk as Boolean, cell as Range
Dim icolor As Integer, rng as Range
If Not Intersect(Target, Range("G13:S17")) Is Nothing Then
bOk = True

set rng = Intersect(Rows("13:17"),Target.EntireColumn)
for each cell in rng.cells
if not isdate(cell.Value) then
if cell.Text <> "N/A" then
bOK = False
exit for
end if
end if
Next
If bOk then
Range("F" & Target.Column).Interior.ColorIndex = 4
Else
Range("F" & Target.Column).Interior.ColorIndex = 0
End If
End if

End Sub

 

[viewmaster]

Hi Tom,

Thanks again for your help! The only problem is that cell in the same
row doesnt change colour another cell changes. For example, if all the
cells in row G12:S12 have "N/A" or a date, F12 is supposed to change
green, however F7 changes. This happens with all the other rows also.
Thanks sooo much for your help!!

 

[viewmaster]

Hi Tom,

Thanks again for your help! The only problem is that cell in the same
row doesnt change colour another cell changes. For example, if all the
cells in row G12:S12 have "N/A" or a date, F12 is supposed to change
green, however F7 changes. This happens with all the other rows also.
Thanks sooo much for your help!!

This is what i have so far:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim bOk As Boolean, cell As Range
Dim icolor As Integer, rng As Range
If Not Intersect(Target, Range("G11:S17")) Is Nothing Then
bOk = True

Set rng = Intersect(Rows("11:17"), Target.EntireColumn)
For Each cell In rng.Cells
If Not IsDate(cell.Value) Then
If cell.Text <> "N/A" Then
bOk = False
Exit For
End If
End If
Next
If bOk Then
Range("F" & Target.Column).Interior.ColorIndex = 4
Else
Range("F" & Target.Column).Interior.ColorIndex = 0
End If
End If

End Sub

 

[viewmaster]

Hi Tom,

Thanks for all your help!!! I worked it out, i just changed

Range("F" & Target.Column).Interior.ColorIndex = 4
to Target.Row....and it works!!

Thanks again!!!

 

[Tom Ogilvy]

It was a guess on what functionality you wanted - guess it was a bad guess,
plus a mental glitch I will have to admit. Sorry for the confusion.

 

[viewmaster]

No worries, thanks to you its working in the first place...so thanks
again!!