Select Current Row when a Condition is True in a For Each Loop

[ScottyBee]

Hello Everyone,

I am trying to format a row in my database when a condition evaluates as true in a For Each Loop for a range. I am close but cannot figure out how to select from the current cell in the loop to the first column in the database. Once I figure that out, I will write some code to format with the appropriate color.

The two parameters being passed to the procedure come from the values contained in two ranges of the same datatype and size. My code is:

Public Sub UpdateTargetData(X As String, Y As String)
    Dim Cell As Range
    Dim CellAddress As String
    For Each Cell In Range("GeneratedColorCodes")
        If Cell.Value = Y Then
            Cell.Value = X
            CellAddress = Cell.Address
            Range("CellAddress").End(xlToLeft).Select
            'Perform some formatting
        End If
    Next Cell
End Sub

When I debug, I get error that the Range("CellAddress").Select cannot be evaluated. However, CellAddress has a value of $K$266 when I hover my mouse over the variable after setting a breakpoint.

So why doesn't Range("CellAddress").Select work whereas typing in Range("K266").Select in the Immedate window does? Thanks

[Paul_Hossler]

Try it without the quotes around CellAddress

The variable CellAddress is a string containing "K266", but by putting quotes around it you make it a string literal

BTW, it's usually not necessary to .Select something to work on it

[ScottyBee]

Hello Paul, that did it. My revised code is:

Public Sub UpdateTargetData(X As String, Y As String)
    Dim Cell As Range
    Dim CellAddress As String
    For Each Cell In Range("GeneratedColorCodes")
        If Cell.Value = Y Then
            Cell.Value = X
            CellAddress = Cell.Address
            Range(CellAddress, Range(CellAddress).End(xlToLeft)).Select
            'Perform some formatting
        End If
    Next Cell
End Sub

However, I do have some blank columns in my database so will need the selection to extend to column A. I do have a named range for my column headings named "ColumnHeadings".

How would I modify my code to not use a Select and work on the range from my defined CellAddress leftwards to column A? Thanks

[jolivanes]

Before you posted Post#3, I wanted to ask you this.
Are the Cell Values X and Y actual alphabetical capital letters? If so, they need to be like "Y" and "X"
Is the Range "GeneratedColorCodes" in a single Column?
You say "Select Current Row when a Condition is True in a For Each Loop" Are you sure you need 16,000 Cells (Excel 2007 and up)
What kind of formatting do you want to perform? And for how many Columns (Assuming not all 16,000+ Cells)
In you code you have "End(xlToLeft)". Do you here mean to go to Column A in the same Row? Or can it be any other Column?

With Range(Cells(CellAddress.Row, 1), Cells(CellAddress.Row, CellAddress.Column))
'Do what needs to be done here
End With

[Paul_Hossler]

Not exactly following.

Can you attach a small sample workbook?

Realistic input data, and clearly identify where you want to go

[ScottyBee]

Hello John and Paul

Thanks for your replies. I have attached my workbook for reference and have included an image of my Excel application. When done, it will contain about 350 rows of data but have deleted most of these for simplicity.

I have a color key in A40:A46. The user can change these to any color they want but need to click the button, "Update Color Codes", to generate the new Hex codes in the range B40:B46

My code finds and replaces the hex codes in column K with the new values that are generated in the range B40:B46. Notice the range F40:F46. These are the hex codes before the button is clicked so my code can identify what has been changed. Notice I changed the last color to blue and the code automatically updated the hex code in B46 to #0070C0 and updated the codes in K31:K35 to this same value. However, the interior color of these rows is still orange.

Now, to my original question in my first post. How do I select from the CellAddress variable that I have to column A? Once I do this, I willl run the hexa_color function against the selection to change the color to blue.

Thank you very much for your assistance.

Public Sub UpdateTargetData(X As String, Y As String)
    Dim Cell As Range
    Dim CellAddress As String
    For Each Cell In Range("GeneratedColorCodes")
        If Cell.Value = Y Then
            Cell.Value = X
            CellAddress = Cell.Address
            Range(CellAddress, Range(CellAddress).End(xlToLeft)).Select
            'Perform some formatting
        End If
    Next Cell
End Sub

Capture.JPG

[Paul_Hossler]

Still not 100% sure I understand, but maybe ....

Option Explicit


Public Sub UpdateTargetData(X As String, Y As String)
    Dim rCell As Range, rColA As Range
    For Each rCell In Range("GeneratedColorCodes").Cells
        If rCell.Value = Y Then
            rCell.Value = X
            Set rColA = rCell.EntireRow.Cells(1)
            
            MsgBox rColA.Address
            'do some formatting
            
            Exit For
        End If
    Next
End Sub

[大灰狼1976]

Maybe...

Range(CellAddress, Cells(Cell.Row, 1)).Select

[ScottyBee]

Hello Paul and 1976,

I incorporated both of your suggestions into my updated code and can now select and apply the new color to the current row in the loop. However, the procedure aborts prematurely with no error and does not continue down to the next cell in the "For Each Loop".

Here is my newest code:

Public Sub UpdateTargetData(X As String, Y As String)
    Dim Cell As Range
    Dim SelectedRange As Range
    Dim CellAddress As String
    For Each Cell In Range("GeneratedColorCodes")
        If Cell.Value = Y Then
            Cell.Value = X
            CellAddress = Cell.Address
            Range(CellAddress, Cells(Cell.Row, 1)).Select
            Set SelectedRange = Selection
            'Perform some formatting
            SelectedRange.Interior.Color = hexa_color(Cell.Value)
            Range(CellAddress).Select
        End If
    Next Cell
End Sub

Here is what the worksheet looks like after the first iteration of the loop:

Capture2.JPG

Just so my desired result is a little more clear, the application allows the users to change colors that they have already applied to the database. In my trouble-shooting step, The orange color in A46 has been changed to Blue. When the user clicks the button "Update Color Codes", my code creates the Hexadecimal color code for newly applied blue in B46. The previously applied color code for the orange is in F46

The code then finds all occurrences of #F4B084 (cells in orange) in a range I named "GeneratedColorCodes which is the column you see labeled "Cell Color" containing all the Hex codes for each row.

Notice that the first cell in the loop was found, changed to the new hex code for blue and the row was formatted blue but the loop aborted without continuing the loop.

Any ideas? Thanks.

[ScottyBee]

Please disregard the post I just submitted. It was user error. It works perfectly.

I did not reset the Hex Codes in K32:K35. They were for a color that was from my earlier debugging that was not orange so there were no orange codes to be found.

Thanks for all of your help.