Create a list in msgbox from Formatted Cells

[keith05281967]

Hello again VBA geniuses,

I've gotten some great feedback from this message board.
I have another question. I have some code that formats the interior color of cells in column A based on certain criteria.

What I'd now like to do is have a message box pop up listing the "values" in those formatted cells. My code is below. The code in red where i'm floundering:

For i = 2 To LastRow
If InStr(1, Cells(i, ColumnNumber).Value, ", AK") <> 0 Or InStr(1, Cells(i, ColumnNumber).Value, ", HI") <> 0 Then
Cells(i, ColumnNumber).Interior.ColorIndex = 4
Cells(i, "A:A").Interior.ColorIndex = 4
intCounter3 = intCounter3 + 1
End If
Next i

If intCounter3 <> 0 Then
MsgBox (intCounter3 & " occurrences of Hawaii, or Alaska state abbv found and hi-lited in green.")
End If

i = 0
For i = 2 To LastRow
If Cells(i, "A:A").Interior.ColorIndex = 4 Then
MsgBox Cells( )
End If
Next i

thanks,
Keith

 

[John_w]

What I'd now like to do is have a message box pop up listing the "values" in those formatted cells.

Perhaps this:

For i = 2 To LastRow
    If Cells(i, "A").Interior.ColorIndex = 4 Then
        MsgBox Cells(i, "A").Value
    End If
Next i

 

[keith05281967]

Thanks John. That was painfully simple. That is progress. Now all i need to do is have it compile the list but I think I can figure that part out.

thanks,
Keith

 

[Peter_SSs]

From what I can see you shouldn't need to loop through all the rows again to get the values - get them on the way through the first time. Something like this:

<font face=Courier New><SPAN style="color:#00007F">Dim</SPAN> sList <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br><br><SPAN style="color:#00007F">For</SPAN> i = 2 <SPAN style="color:#00007F">To</SPAN> LastRow<br>    <SPAN style="color:#00007F">If</SPAN> InStr(1, Cells(i, ColumnNumber).Value, ", AK") <> 0 _<br>            <SPAN style="color:#00007F">Or</SPAN> InStr(1, Cells(i, ColumnNumber).Value, ", HI") <> 0 <SPAN style="color:#00007F">Then</SPAN><br>        Cells(i, ColumnNumber).Interior.ColorIndex = 4<br>        Cells(i, "A").Interior.ColorIndex = 4<br>        sList = sList & vbLf & Cells(i, "A").Value<br>        intCounter3 = intCounter3 + 1<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">Next</SPAN> i<br><br><SPAN style="color:#00007F">If</SPAN> intCounter3 <> 0 <SPAN style="color:#00007F">Then</SPAN><br>    MsgBox intCounter3 & " occurrences of Hawaii, or Alaska state abbv found and hi-lited in green." _<br>        & vbLf & "The Values in column A are:" & sList<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN></FONT>

 

[keith05281967]

Thanks Peter - i'll test this out on Tuesday when i'm back in the office.

 

[keith05281967]

thanks Peter - that works very well.

Keith