PDA

View Full Version : [SOLVED:] Change cell color based on text



RonNCmale
10-02-2014, 06:10 PM
I'm trying to enter vba code for a set range example A1:H30 where if specific text is entered in any cell between those ranges it will change the background color: ie "S" = red, "V" = green, "BIC" = yellow, "A" = pink, "B" = yellow, etc.

So if A is entered into A1 cell the background color will be pink, if B is entered into A1 the background color will be yellow.

jolivanes
10-02-2014, 09:51 PM
Are you able to expand on this?

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Not Intersect(Target, Range("A1:H30")) Is Nothing Then
'If Target = Range("A1:H30") Then
Select Case Target.Value
Case "S"
Target.Interior.Color = vbRed
Case "V"
Target.Interior.Color = vbGreen
Case "BIC"
Target.Interior.Color = vbYellow
Case Else
End Select
End If
End Sub

RonNCmale
10-03-2014, 01:03 AM
This works if I unprotect worksheet, but gives an error "run time error 1004" when the sheet is protected.

Kenneth Hobs
10-03-2014, 05:45 AM
Then use Protect and Unprotect or just use Protect as I detail later.

I like to use this so I don't have to worry about protection issues when my code changes things. It goes in your ThisWorkBook object but the Protect line of code and concept can be used anywhere in your code. Obviously, change ken to whatever you like.


Private Sub Workbook_Open()
Dim ws As Worksheet
For Each ws In Worksheets
ws.Protect "ken", UserInterfaceOnly:=True 'True allows code to change data.
Next ws
End Sub

RonNCmale
10-03-2014, 06:17 AM
Thanks Kenneth