Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB Code Highlight Rows Based on Color Index
Can someone please help me with VB code for the following process:
I have a list of items in column A (a database query linked to Access table). The items are highlighted in different colors by the end-user. When I refresh the database query the next day, the colors move around or dissapear because new items get added to the list. I added a vb function and call it in a hidden column B to display the color index of cell A. If OfText = True Then CellColorIndex = InRange(1, 1).Font.ColorIndex Else CellColorIndex = InRange(1, 1).Interior.ColorIndex End If So, for example, cell A2 is highlighted in yellow, thus cell B2 displays number "6". How can I create a vb code with a loop to go through the rows and highlight the rows based on the value in column B? Hopefully this makes sense. Thank you in advance! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB Code Highlight Rows Based on Color Index
For i = 1 To cells(Rows.Count,"B").End(xlUp).Row rows(i).Interior.Colorindex = Cells(i,"A").Value Next i -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) wrote in message ups.com... Can someone please help me with VB code for the following process: I have a list of items in column A (a database query linked to Access table). The items are highlighted in different colors by the end-user. When I refresh the database query the next day, the colors move around or dissapear because new items get added to the list. I added a vb function and call it in a hidden column B to display the color index of cell A. If OfText = True Then CellColorIndex = InRange(1, 1).Font.ColorIndex Else CellColorIndex = InRange(1, 1).Interior.ColorIndex End If So, for example, cell A2 is highlighted in yellow, thus cell B2 displays number "6". How can I create a vb code with a loop to go through the rows and highlight the rows based on the value in column B? Hopefully this makes sense. Thank you in advance! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB Code Highlight Rows Based on Color Index
Hi akrosita2000 -
Here is a second version that highlights the data range only: Sub akrosarita() Set rng = ActiveSheet.UsedRange.Columns("B").Cells For Each itm In rng With ActiveSheet.UsedRange.Rows(itm.Row - ActiveSheet.UsedRange.Row + 1) .Interior.ColorIndex = itm.Value End With Next 'itm End Sub If you prefer Bob's code, it works fine aside from a minor typo. The term Cells(i, "A").Value should be Cells(i, "B").Value. -- Jay " wrote: Can someone please help me with VB code for the following process: I have a list of items in column A (a database query linked to Access table). The items are highlighted in different colors by the end-user. When I refresh the database query the next day, the colors move around or dissapear because new items get added to the list. I added a vb function and call it in a hidden column B to display the color index of cell A. If OfText = True Then CellColorIndex = InRange(1, 1).Font.ColorIndex Else CellColorIndex = InRange(1, 1).Interior.ColorIndex End If So, for example, cell A2 is highlighted in yellow, thus cell B2 displays number "6". How can I create a vb code with a loop to go through the rows and highlight the rows based on the value in column B? Hopefully this makes sense. Thank you in advance! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB Code Highlight Rows Based on Color Index
Hi Jay,
Here is a second version that highlights the data range only: Did you note that Bob's code restricts operation to the data range of interest. Feasibly, your use of the UsedRange property may be less reliable in this respect. --- Regards, Norman |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB Code Highlight Rows Based on Color Index
Hi Norman -
Thanks for the follow-up. Yes, I see that Bob's approach properly limits the highlights to the rows in the data range. It also highlights the entire worksheet row which is fine, but I thought I'd offer the OP an alternative that also limits the highlighting to the columns in the data range. Regarding your concern, I opted for the UsedRange object to improve portability (the procedure is independent of data location). Based on standard design assumptions (no superfluous data outside the data range - as in Bobs example), Ive found the procedure to be reliable in testing. My testing may have missed something or you may have something more specific (or a generality) in mind; if so, let us know. --- Thanks for the review and discussion, Jay "Norman Jones" wrote: Hi Jay, Here is a second version that highlights the data range only: Did you note that Bob's code restricts operation to the data range of interest. Feasibly, your use of the UsedRange property may be less reliable in this respect. --- Regards, Norman |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB Code Highlight Rows Based on Color Index
Hi Jay,
'----------------- Thanks for the follow-up. Yes, I see that Bob's approach properly limits the highlights to the rows in the data range. It also highlights the entire worksheet row which is fine, but I thought I'd offer the OP an alternative that also limits the highlighting to the columns in the data range. Regarding your concern, I opted for the UsedRange object to improve portability (the procedure is independent of data location). Based on standard design assumptions (no superfluous data outside the data range - as in Bob's example), I've found the procedure to be reliable in testing. My testing may have missed something or you may have something more specific (or a generality) in mind; if so, let us know. '----------------- A potential problem with the use of the UsedRange property is that it may overstate what the user perceives as the worksheets data boundaries. In this connection see Debra Dalgleish's advice on resetting the UsedRange at: http://www.contextures.com/xlfaqApp.html#Unused To limit the number of columns highlighted, a more reliable method might be to return the last data column using, for example, a function like: '========== Function LastCol(SH As Worksheet, _ Optional rng As Range) _ As Long If rng Is Nothing Then Set rng = SH.Cells End If On Error Resume Next LastCol = rng.Find(What:="*", _ After:=rng.Cells(1), _ Lookat:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByColumns, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Column On Error GoTo 0 End Function '<<========== --- Regards, Norman |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB Code Highlight Rows Based on Color Index
Hi Norman
Programmatically referencing the UsedRange appears to automatically disregard the high-water mark left by cleared cells that are located beyond the data range. The VB UsedRange property returns the data-only UsedRange even when the UI does not, i.e., when Ctrl-End senses an empty high water mark. In fact, try this out as a one-line alternative to Debras DeleteUnused procedure to reset the UsedRange after clearing cells to the right and below a data range: Activesheet.UsedRange.Select Once this statement executes, the UsedRange is reset (check it in the UI with Ctrl-End). One advantage of this approach is that its non-destructive; formulas in the data range that reference rows outside the data range are not converted to #Ref! I havent been able to make this statement fail (yet) to reset the UsedRange or fail to directly return the correct data range. I have a theory that perceived unpredictability may be an artifact of the sensitivity of the UI to the high water mark; the VB UsedRange property appears to disregard it. --- Excel2003/WinXP Jay -- Jay "Norman Jones" wrote: Hi Jay, '----------------- Thanks for the follow-up. Yes, I see that Bob's approach properly limits the highlights to the rows in the data range. It also highlights the entire worksheet row which is fine, but I thought I'd offer the OP an alternative that also limits the highlighting to the columns in the data range. Regarding your concern, I opted for the UsedRange object to improve portability (the procedure is independent of data location). Based on standard design assumptions (no superfluous data outside the data range - as in Bob's example), I've found the procedure to be reliable in testing. My testing may have missed something or you may have something more specific (or a generality) in mind; if so, let us know. '----------------- A potential problem with the use of the UsedRange property is that it may overstate what the user perceives as the worksheets data boundaries. In this connection see Debra Dalgleish's advice on resetting the UsedRange at: http://www.contextures.com/xlfaqApp.html#Unused To limit the number of columns highlighted, a more reliable method might be to return the last data column using, for example, a function like: '========== Function LastCol(SH As Worksheet, _ Optional rng As Range) _ As Long If rng Is Nothing Then Set rng = SH.Cells End If On Error Resume Next LastCol = rng.Find(What:="*", _ After:=rng.Cells(1), _ Lookat:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByColumns, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Column On Error GoTo 0 End Function '<<========== --- Regards, Norman |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB Code Highlight Rows Based on Color Index
Hi Jay,
The methods for resetting the UsedRange may be version dependent. If you perform a Google search of the NG archives, you will find considerable previous discussion. Perhaps, however, try: '============= Public Sub Tester() Dim SH As Worksheet Dim Rng As Range Set SH = ActiveSheet With SH Set Rng = .Cells(Rows.Count, Columns.Count) With Rng .NumberFormat = "dd\mm\yy" .Clear End With MsgBox .UsedRange.Address(0, 0) End With End Sub '<<============= As previously indicated, I believe that there may be more reliable ways of establishing the data range than the potentially problematic UsedRange property. --- Regards, Norman "Jay" wrote in message ... Hi Norman - Programmatically referencing the UsedRange appears to automatically disregard the "high-water mark" left by cleared cells that are located beyond the data range. The VB UsedRange property returns the 'data-only' UsedRange even when the UI does not, i.e., when Ctrl-End senses an empty high water mark. In fact, try this out as a one-line alternative to Debra's DeleteUnused procedure to reset the UsedRange after clearing cells to the right and below a data range: Activesheet.UsedRange.Select Once this statement executes, the UsedRange is reset (check it in the UI with Ctrl-End). One advantage of this approach is that it's non-destructive; formulas in the data range that reference rows outside the data range are not converted to "#Ref!" I haven't been able to make this statement fail (yet) to reset the UsedRange or fail to directly return the correct data range. I have a theory that perceived unpredictability may be an artifact of the sensitivity of the UI to the high water mark; the VB UsedRange property appears to disregard it. --- Excel2003/WinXP Jay |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB Code Highlight Rows Based on Color Index
Hi Norman -
Your procedure helps demonstrate that empty, formatted cells contribute to the perception problem associated with resetting the UsedRange object. It appears that resetting the usedrange with ActiveSheet.UsedRange functions properly, but because empty formatted cells are included in the usedrange, the reset can have unexpected consequences if a user is unaware of this. As you stated in your first post, this manifests itself as a reliability concern. Thanks for the discussion; for me, it has clarified prior discussions on the issue. --- Jay (Student of the UsedRange) "Norman Jones" wrote: Hi Jay, The methods for resetting the UsedRange may be version dependent. If you perform a Google search of the NG archives, you will find considerable previous discussion. Perhaps, however, try: '============= Public Sub Tester() Dim SH As Worksheet Dim Rng As Range Set SH = ActiveSheet With SH Set Rng = .Cells(Rows.Count, Columns.Count) With Rng .NumberFormat = "dd\mm\yy" .Clear End With MsgBox .UsedRange.Address(0, 0) End With End Sub '<<============= As previously indicated, I believe that there may be more reliable ways of establishing the data range than the potentially problematic UsedRange property. --- Regards, Norman "Jay" wrote in message ... Hi Norman - Programmatically referencing the UsedRange appears to automatically disregard the "high-water mark" left by cleared cells that are located beyond the data range. The VB UsedRange property returns the 'data-only' UsedRange even when the UI does not, i.e., when Ctrl-End senses an empty high water mark. In fact, try this out as a one-line alternative to Debra's DeleteUnused procedure to reset the UsedRange after clearing cells to the right and below a data range: Activesheet.UsedRange.Select Once this statement executes, the UsedRange is reset (check it in the UI with Ctrl-End). One advantage of this approach is that it's non-destructive; formulas in the data range that reference rows outside the data range are not converted to "#Ref!" I haven't been able to make this statement fail (yet) to reset the UsedRange or fail to directly return the correct data range. I have a theory that perceived unpredictability may be an artifact of the sensitivity of the UI to the high water mark; the VB UsedRange property appears to disregard it. --- Excel2003/WinXP Jay |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
highlight subtotal rows with a fill color | Excel Worksheet Functions | |||
Color Index Code, is there any thing like that? | New Users to Excel | |||
Should be able to color code or highlight Excel worksheet tabs. | Excel Worksheet Functions | |||
Range based on interior color index? | Excel Programming | |||
excel: How can I color code rows based on a value in a cell? | Excel Discussion (Misc queries) |