2

As above.

I would like to use this:

colnum = ws.UsedRange.Find(what:=Sheet11.Range("B1"), lookat:=xlWhole).Column

Not to find Sheet11.Range("B1") but instead the color, and only IF the entire column is colored in red.

Any suggestions?

Community
  • 1
  • 1
Saverio
  • 111
  • 8

1 Answers1

4

You can use, for example, if Range("B:B").Interior.Color = RGB(255, 0, 0)

See this question for more detail VBA to identify cells in red

EDIT:

I'm not sure what usedRange refers to, but hopefully this example will give you what you need. We loop through the columns finding a match - I am not aware of a better way

Sub Macro1()
    clr = Sheets("Sheet11").Range("B1").Interior.Color

    For c = 1 To 100 'however many columns you may need to search ...
        If ActiveSheet.Columns(c).Interior.Color = clr Then
            ' set whichever cell you want = c 
        End If
    Next c
End Sub
ifo20
  • 738
  • 8
  • 20
  • @ifo20 I figured I'd be able to find a reason it won't work to check the entire column, but I can't. Nicely done+ – ashleedawg Jul 05 '18 at 18:15
  • not sure how use this @ifo20. In sheet11.range(b1) i have a number. Then in the active sheet (which is not sheet11) I search this number and then perform some operations. but i want to change the number with a color. the algorithm would be: identify color of sheet11.range(b1)>go to active sheet>find in used range the entire column which is colored with the same color as sheet11.range(b1)>get the column number – Saverio Jul 06 '18 at 07:51
  • I have updated my answer to demonstrate how to achieve the algorithm you mentioned ... does this make sense now? – ifo20 Jul 06 '18 at 08:33
  • Nice done man, works perfectly. And I feel little stupid because it was particularly simple :) Thanks! – Saverio Jul 06 '18 at 09:14