Select Current Row when a Condition is True in a For Each Loop
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:
Code:
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
2 Attachment(s)
Excel code and Image are Attached
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.
Code:
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
Attachment 25011
1 Attachment(s)
Almost There, Loop Aborts Prematurely with No Error
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:
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:
Attachment 25040
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.