Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Default colour in cell based on specific entry | Excel Worksheet Functions | |||
Default Cell border colour? | Excel Discussion (Misc queries) | |||
Update Macro: Leave Colour As Before Once Cell is Not Active | Excel Discussion (Misc queries) | |||
How do you change the default colour a cell is highlighted in? | Excel Discussion (Misc queries) | |||
Macro to sort by cell colour | Excel Worksheet Functions |