Solved

Enter cell with specific term conditionally

Posted on 2014-07-22
8
111 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 46

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 46

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
Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

 

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 32

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 46

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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
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 …
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

776 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