Andreas Hermle
asked on
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
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
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
ASKER
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
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
ASKER
Ok, another try delivered the right line of code:
For Each cel In ActiveSheet.UsedRange.Colu mns("A").C ells
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
For Each cel In ActiveSheet.UsedRange.Colu
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
ASKER
Martin, one question before awarding the points: Is there a non-VBA-solution for my task?
Regards, Andreas
Regards, Andreas
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
Thanks
Rob H
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi rob thank you for bringing this to my attention this may come in handy in other tasks
Open in new window