VB Macro for cell colour default
 

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


--
KCG

VB Macro for cell colour default
 

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




"KCG" wrote:

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


--
KCG

VB Macro for cell colour default
 

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



KCG wrote:

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

--
KCG

--

Dave Peterson