VB Code Highlight Rows Based on Color Index

[akrosita2000]

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!

 

[Bob Phillips]

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)

 

[Jay]

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.

 

[Norman Jones]

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.

 

[Jay]

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

 

[Norman Jones]

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
'<<==========

 

[Jay]

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.

 

[Norman Jones]

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.

 

[Jay]

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)

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.