VB Macro for cell colour default

[Guest]

Hello there,

I have used up all conditional formatting. Please help me with developing a
Macro for further cell formatting as follows:

I have this range of numbers in the row A10:G10

12 5 26 3 17 38 9 23

If any number in this list is in the range A1:G9, I need the macro to colour
that found value's cell, blue.

Thanx for your help.

Regards

 

[Guest]

Try this. Right click on the worksheet tab that you want this in and paste
it into the worksheet module. If you want to change the colors, here's a
magic decoder ring:

http://www.mvps.org/dmcritchie/excel/colors.htm

Private Sub Worksheet_Change(ByVal Target As Range)
Dim aWS As Worksheet
Dim myRange As Range
Dim r As Range
Dim r1 As Range
Dim myCheckRange As Range

Set aWS = Target.Parent
Set myRange = aWS.Range("A10:G10")
If Not Intersect(Target, myRange) Is Nothing Then
Set myCheckRange = aWS.Range("A1:G9")
For Each r In myRange
For Each r1 In myCheckRange
If r.Value = r1.Value And Not IsEmpty(r) Then
Debug.Print r.Value
r.Interior.ColorIndex = 20
End If
Next r1

Next r

End If


End Sub

 

[Dave Peterson]

Another one:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim myValRng As Range
Dim myCell As Range
Dim myRngToCheck As Range
Dim myInterSect As Range
Dim res As Variant

Set myValRng = Me.Range("a10:g10")
Set myRngToCheck = Me.Range("a1:g9")

Set myInterSect = Intersect(Target, myRngToCheck)

If myInterSect Is Nothing Then Exit Sub

For Each myCell In myInterSect.Cells
res = Application.Match(myCell.Value, myValRng, 0)
If IsError(res) Then
myCell.Interior.ColorIndex = xlNone
Else
myCell.Interior.ColorIndex = 20
End If
Next myCell
End Sub