Please help. Thanks!

  • Thread starter Thread starter Damil4real
  • Start date Start date
D

Damil4real

I have a worksheet named “Sheet16” and I have several data in column
B. I want to create a macro that will enable me to pop up a message
that will indicate which of the numbers in column B are not colored in
Red. Basically, there is a list of numbers in column B all colored in
red, I want to be able to create a macro that will indicate which ones
are not colored in red by popping up a message box that tells me, when
the macro is clicked.

I hope you get what I’m trying to do.

Thanks!
 
First you need to confirm the color. There can be several shades of red.

Run this little macro first and record the number.

Sub testcolor()

MsgBox "Font code for color is: " & ActiveCell.Font.Color

End Sub

Now this is your macro. Note the comment.

Sub ListNotRed()
Dim rngColB As Range
Dim c As Range
Dim strList As String

With Sheets("Sheet16")
Set rngColB = Range(.Cells(1, "B"), _
.Cells(.Rows.Count, "B").End(xlUp))
End With

rngColB.Select

For Each c In rngColB
'Replace 255 in the following line with your color code
If c.Font.Color <> 255 Then
strList = strList & c.Value & ", "
End If
Next c

If Len(strList) > 2 Then
strList = Left(strList, Len(strList) - 2)
End If

MsgBox strList

End Sub
 
using form create a button and assign a macro

Sub no_color()
mycol = 2
For cell = 1 To Cells(Rows.Count, 2).End(xlUp).Row
If Cells(cell, mycol).Interior.ColorIndex = 3 Then
Cells(cell + 1, mycol).Select
Else
MsgBox ActiveCell.Address
Cells(cell + 1, mycol).Select
End If
Next cell
End Sub
 
I should have added that you can change the following line to return the row
numbers where the numbers are not red instead of the actual value of the
cells.

change this line: strList = strList & c.Value & ", "
to this: strList = strList & c.Row & ", "

Also I assumed that it is the numbers that are red not the background of the
cell. Is this assumption correct?
 
I should have added that you can change the following line to return the row
numbers where the numbers are not red instead of the actual value of the
cells.

change this line:  strList = strList & c.Value & ", "
to this:                strList = strList & c.Row & ", "

Also I assumed that it is the numbers that are red not the background of the
cell. Is this assumption correct?
Click to expand...

Thanks, OssieMac! Code worked great! Though my related to the
background color of the cell.

How about if I want this code to apply to two columns instead of just
one? A & B? Or three column? A, B, & C?

Thanks!
 
I should have added that you can change the following line to return the row
numbers where the numbers are not red instead of the actual value of the
cells.

change this line:  strList = strList & c.Value & ", "
to this:                strList = strList & c.Row & ", "

Also I assumed that it is the numbers that are red not the background of the
cell. Is this assumption correct?
Click to expand...

Also, thanks for your help, Muddan! Your code moves from cell to cell.
Isn't there a way it can just list the cells in one message box? And
how does it apply to 2 or 3 columns?
 
Back
Top