0

In a range of 10 cells i have 2 cells of red colors.

Like I can identify the blank cells using below

Dim rng As Range

Set rng = Range("A1:A10").SpecialCells(xlCellTypeBlanks)

rng.Select

Is there a way to identify cells of specific format (in this case cells with Red color)

as specialcells does not show any relevant option.

I want to identify these red cells in a range preferably without any loop.

Thanks for looking into it.

Community
  • 1
  • 1
  • [duplicate](http://stackoverflow.com/questions/16330345/vba-to-identify-cells-in-red) –  May 07 '13 at 11:52
  • @mehow Is it really ? – Santosh May 07 '13 at 11:55
  • 1
    Y downward????...Kindly read my question properly.... –  May 07 '13 at 11:57
  • @Santosh dont you recon? –  May 07 '13 at 11:57
  • @mehow I dont think so :) – Santosh May 07 '13 at 11:59
  • @user2230817 Without loop i dont think so its possible. – Santosh May 07 '13 at 12:10
  • 1
    I'm pretty sure this can't be done without looping through the range. Any reason you can't / don't want to loop? With a range that small (even a range 10x that), if done properly, won't take any noticeable time. – sous2817 May 07 '13 at 12:12
  • even calling an excel built in function the looping exist - it just happens behind the scenes - but it loops –  May 07 '13 at 12:14
  • 1
    @mehow as in this case `Range("A1:A10").SpecialCells(xlCellTypeBlanks)` behind the scenes loop will be called and OP wants that. Why dont you read the question? – Santosh May 07 '13 at 12:19
  • @Santosh because what hes asking for doesnt exist thats why its a duplicate –  May 07 '13 at 12:25
  • you can do it in two steps but still involves looping. Find what you want save it as string then select range from a string like `Array("row:row","row2:row2")` to have just the selection done in one go –  May 07 '13 at 12:31
  • 1
    @mehow if something doesnot exist you will downvote and mark it as duplicate ? Does that make any sense? I believe even OP is aware of the issue and wants any alternative solutions from this forum. – Santosh May 07 '13 at 12:41
  • @mehow, the OP isn't asking how to prevent Excel doing a behind the scenes loop in the background memory, he is asking if it's possible to do what he wants without looping in VBA ... – Our Man in Bananas May 07 '13 at 13:14

1 Answers1

0

altering a color in a cell is non-volatile - that means that cells depending on the color will not be updated if their reference cell changes.

Having said that, and also having recommended against using the color to make a decision (when you should try to use the value) you could try the below:

  1. see Using old XLM GET.CELL (function 63) function to reference color
  2. use that function to create an #NA value in the cell (so =IF(...,NA(),"") maybe?
  3. Use SpecialCells to return the cells with #NA (see here for some tips)

I hope that gets you started, at least with some reading that might point you in the right direction.

But again, you should try to make your decision based on a cells value, not formatting

Community
  • 1
  • 1
Our Man in Bananas
  • 5,809
  • 21
  • 91
  • 148