Solved

Enter cell with specific term conditionally

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

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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
User Form VBA Data + Print Sections 39 114
ms office troubleshooting for users 8 36
Highlighting cells in Excel 9 17
Fixing a embedded format 7 29
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,…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

863 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now