Solved

Enter cell with specific term conditionally

Posted on 2014-07-22
8
115 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
  • 4
  • 3
8 Comments
 
LVL 47

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 47

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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 

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 47

Accepted Solution

by:
Martin Liss earned 500 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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

730 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