VBA code to apply text to a cell based on its shade color.

Hi I'd like to see if there is some simple VBA code I can use to says in sheet "Jan" find the first cell in column H that is shaded in yellow and apply the text to it that is in cell C2. I unfortunately can't use formulas directly on the sheet because all of the cells on the sheet end up as pasted values.

Thanks for any help!!

Originally Posted by nathansav

for each c in range("H:H").cells
if c.interior.colorindex=??????? then
c.text = range("c2").text
exit for
end if
next c

Hi Nathan,

Thanks for the help. I'm getting a "Compile Error: Variable Not Defined" error when trying to run that code. I enclosed it in Sub and End Sub, and filled in colorindex with 6. What else might I be missing? Sorry, I'm still a little new to all this. Thanks again!

Ok, added the dim c statement and not getting any more errors. Thanks for that. However I'm also not getting any results, and I imagine it's because my colorindex number isn't correct. Any idea what the value of that should be for the standard yellow shading?

Thanks again,

Originally Posted by nathansav

format it on the sheet. then in VBA in the immediate window, type activecell.interior.colorindex.

"Compile Error: Expected: identifier or bracketed expression"

Originally Posted by nathansav

:o)

the full stop was the end of the line, so you need

? activecell.interior.colorindex

Ok great, thanks for sticking with me here - please continue to bear with me here if you will

So it brings back 6 for the value of standard yellow which is what I thought it was. However I'm still not getting any results. This is the exact code I have:

Please Login or Register  to view this content.

No errors when I run it, but doesn't put the text from cell c2 into the one yellow shaded cell in column H. Any ideas? I've gone ahead and attached it for your reference.

Thanks again!

By the way, I have no idea if this makes any difference, but the yellow shaded cells in the Sheet are conditionally formatted.

Originally Posted by nathansav

ive just ran it and its fine, so i think it might be,

?.........

VBA can't evaluate colors set by conditional formatting. You have to build logic into the code that is the same as your conditional formatting if you want to use that in your code.

Originally Posted by JOHN H. DAVIS

VBA can't evaluate colors set by conditional formatting. You have to build logic into the code that is the same as your conditional formatting if you want to use that in your code.

Ok, thank you for that info, John.

Another way I can approach it is to apply the text to column H where column A of the same row contains "Grand Total". Might you be able to help me with such code?

Many thanks!

Based on your previous code, maybe:

Please Login or Register  to view this content.

Originally Posted by JOHN H. DAVIS

Based on your previous code, maybe:

Please Login or Register  to view this content.

Hi again John, thanks for the help.

There must be something wrong with the line 'If Cells(c.Row, "A") = "Grand Total" Then' - I'm getting a "Run-time Error '424' -Object Required."

Any ideas?

Try:

Please Login or Register  to view this content.

Originally Posted by JOHN H. DAVIS

Try:

Please Login or Register  to view this content.

John, works perfectly!

Thank you so very much for your help on this one!

You're welcome. Glad to help out and thanks for the feedback.