Does anyone have any suggesitons on how to do it for worksheet?
 

Does anyone have any suggesitons on how to do it for worksheet?

Condition 1:
In cell A1, there is a number 1234, under the range between cell B2 to Z50,
if any cell matches the number 1234 in cell A1, then it's background color
should change to red and it's text color should change to white.
Condition 2:
In cell A2, there is a number 2345, under the range between cell B2 to Z50,
if any cell matches the number 2345 in cell A2, then it's background color
should change to blue and it's text color should change to white.

Condition 3:
In cell A3, ....
....

Condition 10:
In cell A10, ....
There is a priority for a list conditions,
If condition 2,3,5,6,8,10 are false, condition 1,4,7,9 are true, only change
the color for condition 1.
If condition 1,2,3,5,6,8,10 are false, condition 4,7,9 are true, only change
the color for condition 4.

I cannot use Excel build-in function to change the background and text
color, because it is limited to 3 different conditions only, but I get 10
conditions.
Does anyone have any suggestions on how to code macro for worksheet to
change the background and text color?
Thanks in advance for any suggestions
Eric

Does anyone have any suggesitons on how to do it for worksheet?
 

Hi Eric:

When you say:

"If condition 2,3,5,6,8,10 are false"

do you mean if ALL are False or if ANY are False??
--
Gary''s Student - gsnu200834


"Eric" wrote:

Does anyone have any suggesitons on how to do it for worksheet?

Condition 1:
In cell A1, there is a number 1234, under the range between cell B2 to Z50,
if any cell matches the number 1234 in cell A1, then it's background color
should change to red and it's text color should change to white.
Condition 2:
In cell A2, there is a number 2345, under the range between cell B2 to Z50,
if any cell matches the number 2345 in cell A2, then it's background color
should change to blue and it's text color should change to white.

Condition 3:
In cell A3, ....
...

Condition 10:
In cell A10, ....
There is a priority for a list conditions,
If condition 2,3,5,6,8,10 are false, condition 1,4,7,9 are true, only change
the color for condition 1.
If condition 1,2,3,5,6,8,10 are false, condition 4,7,9 are true, only change
the color for condition 4.

I cannot use Excel build-in function to change the background and text
color, because it is limited to 3 different conditions only, but I get 10
conditions.
Does anyone have any suggestions on how to code macro for worksheet to
change the background and text color?
Thanks in advance for any suggestions
Eric

Does anyone have any suggesitons on how to do it for worksheet?
 

Modify to suit

Sub ColorNumbers()
For Each n In Range("a1:a3")
With Range("b1:b50")
Set c = .Find(n, LookIn:=xlValues, LookAt:=xlWhole, _
SearchOrder:=xlByRows, SearchDirection:=xlNext)
If Not c Is Nothing Then
firstAddress = c.Address
Do
Select Case c
Case 1234: ic = 3: fc = 2
Case 5678: ic = 5: fc = 2
'etc
Case Else: ic = xlNone: fc = 0
End Select

c.Interior.ColorIndex = ic
c.Font.ColorIndex = fc
c.Font.Bold = True

Set c = .FindNext(c)
Loop While Not c Is Nothing And _
c.Address < firstAddress
End If
End With

Next n
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Eric" wrote in message
...
Does anyone have any suggesitons on how to do it for worksheet?

Condition 1:
In cell A1, there is a number 1234, under the range between cell B2 to
Z50,
if any cell matches the number 1234 in cell A1, then it's background color
should change to red and it's text color should change to white.
Condition 2:
In cell A2, there is a number 2345, under the range between cell B2 to
Z50,
if any cell matches the number 2345 in cell A2, then it's background color
should change to blue and it's text color should change to white.

Condition 3:
In cell A3, ....
...

Condition 10:
In cell A10, ....
There is a priority for a list conditions,
If condition 2,3,5,6,8,10 are false, condition 1,4,7,9 are true, only
change
the color for condition 1.
If condition 1,2,3,5,6,8,10 are false, condition 4,7,9 are true, only
change
the color for condition 4.

I cannot use Excel build-in function to change the background and text
color, because it is limited to 3 different conditions only, but I get 10
conditions.
Does anyone have any suggestions on how to code macro for worksheet to
change the background and text color?
Thanks in advance for any suggestions
Eric