PDA

View Full Version : [SOLVED:] Help Writing Code That is Conditional on Shading of Cell


mikeoly
12-14-2015, 09:55 AM
Hi everyone,
I was hoping there was a formula that would enable me to do what I want, but it seems like I'll have to use VBA.

Goal: If cell is shaded grey, take no action, if cell is not shaded, then concatenate columns C + B and do a vlookup.

I've looked through the forum but couldn't find what I needed. Any help would be greatly appreciate!

Thanks guys!! :thumb
Paul_Hossler
12-14-2015, 11:07 AM
If cell is shaded grey,

There are lots of gray shades, and many look alike, but might be slightly different. How did the cell get gray?


Can you post a small sanitized example workbook?
mikeoly
12-14-2015, 11:18 AM
There are lots of gray shades, and many look alike, but might be slightly different. How did the cell get gray?


Can you post a small sanitized example workbook?
Sure! Cells are greyed out by another party working in the same spreadsheet. If cell is grey, then the cell contents should be zero. Everything else needs to be updated via vlookup.

14979
Paul_Hossler
12-14-2015, 07:37 PM
What's the vlookup logic nd do you really want to VLookup 1000 & Marine?

Instead of testing for .ColorIndex = 15 (Gray in your sample WB) to ignore, could you test for xlColorIndexNone and process them i.e. the uncolored ones? Since there could be many shades of Gray, it might be safer
mikeoly
12-15-2015, 03:23 PM
What's the vlookup logic nd do you really want to VLookup 1000 & Marine?

Instead of testing for .ColorIndex = 15 (Gray in your sample WB) to ignore, could you test for xlColorIndexNone and process them i.e. the uncolored ones? Since there could be many shades of Gray, it might be safer

I should've been clearer. I plan to concatenate the product & year (e.i. Marine1998, Marine 1999, ....) to create a unique key and then use that to lookup a specific column in another sheet.

REVISION TO ORIGINAL THOUGHT: Instead of referencing the color, I'll use the same logic above to lookup and identify whether a value (in the other sheet) is less than 0.1. I'll use that to determine whether to input a zero or not rather than referencing the cell color.