Changing cell colour in Range

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

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

--
Regards,
Tom Ogilvy


"viewmaster" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>
>
> --
> viewmaster
> ------------------------------------------------------------------------
> viewmaster's Profile:
http://www.excelforum.com/member.php...o&userid=32094
> View this thread: http://www.excelforum.com/showthread...hreadid=519227
>

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!!

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

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!!!

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.

--
Regards,
Tom Ogilvy


"viewmaster" <[email protected]> wrote
in message news:[email protected]...
>
> 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!!!
>
>
> --
> viewmaster
> ------------------------------------------------------------------------
> viewmaster's Profile:
http://www.excelforum.com/member.php...o&userid=32094
> View this thread: http://www.excelforum.com/showthread...hreadid=519227
>

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