Enter cell with specific term conditionally

Dear Experts:

on the selected cells (contiguous selection) I would like to run the following macro:

If the macro detects cells shaded with RGB (211, 211, 211) the term 'Item_No' is to be entered
If the macro detects cells shaded with RGB (195, 187, 132) the term 'Stock' is to be entered
Any other shading no entry has to be effected.

Help is much appreciated. Thank you very much in advance. Regards, Andreas
Andreas HermleTeam leaderAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Martin LissOlder than dirtCommented:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Interior.Color = RGB(211, 211, 211) Then
    ActiveCell = "Item_No"
ElseIf Target.Interior.Color = RGB(195, 187, 132) Then
    ActiveCell = "Stock"
End If
End Sub

Open in new window

0
Martin LissOlder than dirtCommented:
Sorry, I see you wanted a macro.

Sub AddText()

Dim cel As Range

For Each cel In ActiveSheet.UsedRange
    If cel.Interior.Color = RGB(211, 211, 211) Then
        cel = "Item_No"
    ElseIf cel.Interior.Color = RGB(195, 187, 132) Then
        cel = "Stock"
    End If
Next
End Sub

Open in new window

0
Andreas HermleTeam leaderAuthor Commented:
Hi Martin,

ok, works great, thank you very much, but I forgot to tell you that it should only be run in Column A

I tried 'For Each cel In ActiveSheet.Columns(1)' but this strangely did not work.

Thank you, Regards, Andreas
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

Andreas HermleTeam leaderAuthor Commented:
Ok, another try delivered the right line of code:

For Each cel In ActiveSheet.UsedRange.Columns("A").Cells
    If cel.Interior.Color = RGB(195, 195, 195) Then
        cel = "Bestand"
    ElseIf cel.Interior.Color = RGB(229, 229, 229) Then
        cel = "Disponent"
    End If
Next
End Sub
0
Andreas HermleTeam leaderAuthor Commented:
Martin, one question before awarding the points: Is there a non-VBA-solution for my task?

Regards, Andreas
0
Rob HensonFinance AnalystCommented:
Andreas, turning this on its head. Rather than checking the formatting to create an entry, why not check the entry to decide the formatting. This would use the Conditional Formatting feature and would not require any VBA.

Thanks
Rob H
0
Martin LissOlder than dirtCommented:
This is limited to column A. And no I don't know any non-VBA way except for Rob Henson's suggestion which would IMO be harder then a manual process.

Sub AddText()

Dim cel As Range

For Each cel In Range("A1:A" & ActiveSheet.UsedRange.Rows.Count)
    If cel.Interior.Color = RGB(211, 211, 211) Then
        cel = "Item_No"
    ElseIf cel.Interior.Color = RGB(195, 187, 132) Then
        cel = "Stock"
    End If
Next
End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Andreas HermleTeam leaderAuthor Commented:
Hi rob thank you for bringing this to my attention this may come in handy in other tasks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.