conditional formatting

  • Thread starter Thread starter jamesjohn
  • Start date Start date
J

jamesjohn

Can anyone help ?

I have a matrix of numbers:

121 122 123 124 125
221 222 223 224 225

etc. which represent room numbers in a hotel.

I need to enter a room number in a new cell and format th
corresponding number in the matrix. BUT i want the format to "stick
when I input the next number. At the moment if I write 121, then th
121 in the matrix turns red. Good, but when I enter 124 then the 121 i
the matrix becomes normal and 124 turns red. I need the formatting t
stay.

Don't know if there's a solution ?

Cheers,
Jame
 
Hi
this would require VBA (using an event procedure) Would this be a
feasible way for you?
 
Hi
in addition to give you an example. If your input cell is G1 and your
matrix A1:F10 try the following macro (put this in your worksheet
module, not in a standard module):

Private Sub Worksheet_Change(ByVal Target As Range)
Dim search_rng As Range
Dim f_range As Range
Dim firstaddress

If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("G1")) Is Nothing Then Exit Sub
Set search_rng = Me.Range("A1:F10")
With Target
If .Value <> "" Then
Set f_range = search_rng.Find(.Value, LookIn:=xlValues,
lookat:=xlWhole)
If Not f_range Is Nothing Then
firstaddress = f_range.Address
Do
f_range.Interior.ColorIndex = 3
Set f_range = search_rng.FindNext(f_range)
Loop While Not f_range Is Nothing And f_range.Address _
<> firstaddress
End If
End If
End With
End Sub
 
Thanks Frank.

It works well. Just 2 things:

1) How can I get the formatting to change when I press enter ?
2) Am I only able to have 1 SelectionChange item in each worksheet ?

I ask this because I want to have different colours for differen
groups, maybe up to 15 in number.

I edited the code you sent me so that there was a different input cel
and a different colour, but I can't get it to work.

Thanks in advance,

Jame
 
Hi James
you can only have one Selection_change procedure per sheet but within
this you can distinguish between different cells/formats.

For your other questions I'm not so sure what you're trying exactly to
do. Also you may explain what does not work for you
 
Hi Frank,

At the moment if I enter a value in cell C12, then the relative cell i
the matrix changes colour.

I now need to extend it so that if I enter a value in cell C11, th
relative cell in the same matrix changes to another colour and so on u
to about C20.

I realise there must be some way to nest these instructions but I a
unable to do it.

Thanks again in advance,

Cheers Jame
 
Hi
try something like the following:
- lookup range in A1:B20
- your values to enter in cells C11:C20


Private Sub Worksheet_Change(ByVal Target As Range)
Dim search_rng As Range
Dim f_range As Range
Dim firstaddress
Dim col_index

If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("C1:C20")) Is Nothing Then Exit Sub
Set search_rng = Me.Range("A1:B20")
With Target
If .Value <> "" Then
Set f_range = search_rng.Find(.Value, LookIn:=xlValues, _
lookat:=xlWhole)
If Not f_range Is Nothing Then
firstaddress = f_range.Address
select case target.address
case $C$11
col_index=3
case $C$12
col_index=4
'....
case $C$20
col_index=10
end select
Do
f_range.Interior.ColorIndex = col_index
Set f_range = search_rng.FindNext(f_range)
Loop While Not f_range Is Nothing And f_range.Address _
<> firstaddress
End If
End If
End With
End Sub
 
Back
Top