?
Solved

Enter cell with specific term conditionally

Posted on 2014-07-22
8
Medium Priority
?
121 Views
Last Modified: 2014-07-27
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
0
Comment
Question by:AndreasHermle
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
8 Comments
 
LVL 48

Expert Comment

by:Martin Liss
ID: 40212278
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
 
LVL 48

Expert Comment

by:Martin Liss
ID: 40212287
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
 

Author Comment

by:AndreasHermle
ID: 40213498
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:AndreasHermle
ID: 40213510
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
 

Author Comment

by:AndreasHermle
ID: 40213519
Martin, one question before awarding the points: Is there a non-VBA-solution for my task?

Regards, Andreas
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 40213815
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
 
LVL 48

Accepted Solution

by:
Martin Liss earned 2000 total points
ID: 40214292
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
 

Author Comment

by:AndreasHermle
ID: 40222247
Hi rob thank you for bringing this to my attention this may come in handy in other tasks
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question