Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 -- viewmaster ------------------------------------------------------------------------ viewmaster's Profile: http://www.excelforum.com/member.php...o&userid=32094 View this thread: http://www.excelforum.com/showthread...hreadid=519227 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing cell colour in Range
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" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing cell colour in Range
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 ------------------------------------------------------------------------ viewmaster's Profile: http://www.excelforum.com/member.php...o&userid=32094 View this thread: http://www.excelforum.com/showthread...hreadid=519227 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing cell colour in Range
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 ------------------------------------------------------------------------ viewmaster's Profile: http://www.excelforum.com/member.php...o&userid=32094 View this thread: http://www.excelforum.com/showthread...hreadid=519227 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing cell colour in Range
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing cell colour in Range
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" wrote in message ... 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing cell colour in Range
No worries, thanks to you its working in the first place...so 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Changing the colour of a range of cells, some with formulas. | Excel Worksheet Functions | |||
Changing background colour when changing data in a cell | Excel Discussion (Misc queries) | |||
Changing fill colour of cells in a range | Excel Discussion (Misc queries) | |||
Changing a cell colour | Excel Discussion (Misc queries) | |||
Changing Cell Colour | Excel Programming |