Macro row and column conditional formating problem
 

Hi, I have a large matrix ( 1000x50) of test groups against test types where
the grid contains imported test results (eg 1=Not Tested, 2=Passed, 3=Failed
etc - it's more extensive than this but you see what I mean). I want to
easily relate specific test results to groups and types. I have a
supplementary grid beside my grid checking for the existance of the test
results (1's, 2's etc) in the rows and another below my grid checking for the
existance of the test results (1's, 2's etc) in the columns. I want to colour
all the rows AND all the columns where test results (say 1's - Not Tested's)
occur. The trouble is, I can do all the checks OK but can only colour rows OR
columns, but not both at the same time. The following is a copy of 2 of my
macro codes (for a simplified (5x5 matrix)) doing horizontal and vertical
checks and colouring for the number 1. If I combine them the vertical
colouring erases the horizontal colouring, so I currently have the macro's
assigned to seperate control buttons and only get half the picture. I have a
macro that identifies all the solitary appearances of (say) 1's, but I need a
macro that combines the following 2 macros without it erasing the colouring
it has done in the early part of the macro. Once I crack this for the 5x5
matrix I'm sure I can expand it for my actual matrix.

My 2 macros a-

Sub HorizontalCheck1()
'
' HorizontalCheck1 Macro
Range("B2:F2").Select 'Select whole of first row
Selection.FormatConditions.Delete 'Delete formatting in first row
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=COUNTIF($AA$2,""yes"")" 'CF - AA2 = checking for answer 1's in row 1
Selection.FormatConditions(1).Interior.ColorIndex = 4 'Colour first row
if 1 in it

Range("B3:F3").Select 'Select whole of 2nd row
Selection.FormatConditions.Delete 'Delete formatting in 2nd row
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=COUNTIF($AA$3,""yes"")" 'CF - AA3 = checking for answer 1's in row 2
Selection.FormatConditions(1).Interior.ColorIndex = 4 'Colour 2nd row if
1 in it
End Sub
Sub VerticalCheck1()
'
' VerticalCheck1 Macro
Range("B2:B5").Select 'Select whole of first column
Selection.FormatConditions.Delete 'Delete formatting in first column
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=COUNTIF($B$100,""yes"")" 'CF - B100 = checking for answer 1's in
column 1
Selection.FormatConditions(1).Interior.ColorIndex = 4 'Colour first
column if 1 in it

Range("C2:C5").Select 'Select whole of 2nd column
Selection.FormatConditions.Delete 'Delete formatting in 2nd column
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=COUNTIF($C$100,""yes"")" 'CF - C100 = checking for answer 1's in
column 2
Selection.FormatConditions(1).Interior.ColorIndex = 4 'Colour 2nd column
if 1 in it
End Sub
--
Many TIA and sorry for the long problem description.
Trevor

Macro row and column conditional formating problem
 

Sub HorizontalCheck1()
Range("B2:F6").Select
Selection.FormatConditions.Delete 'Delete formatting in first row
' HorizontalCheck1 Macro
Range("B2:F2").Select 'Select whole of first row
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=COUNTIF($AA$2,""yes"")" 'CF - AA2 = checking for answer 1's in row
1
Selection.FormatConditions(1).Interior.ColorIndex = 4 'Colour first row
if 1 in it

Range("B3:F3").Select 'Select whole of 2nd row
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=COUNTIF($AA$3,""yes"")" 'CF - AA3 = checking for answer 1's in row
2
Selection.FormatConditions(1).Interior.ColorIndex = 4 'Colour 2nd row if
1 in it
End Sub
Sub VerticalCheck1()
Dim cell As Range, cnt As Long
'
' VerticalCheck1 Macro
Range("B2:B5").Select 'Select whole of first column
' Selection.FormatConditions.Delete 'Delete formatting in first column
For Each cell In Selection
cnt = cell.FormatConditions.Count
cell.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=COUNTIF($B$100,""yes"")" 'CF - B100 = checking for answer 1's in
Column 1
cell.FormatConditions(cnt + 1).Interior.ColorIndex = 4 'Colour first
column if 1 in it
Next

Range("C2:C5").Select 'Select whole of 2nd column
For Each cell In Selection
cnt = cell.FormatConditions.Count
' Selection.FormatConditions.Delete 'Delete formatting in 2nd column
cell.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=COUNTIF($C$100,""yes"")" 'CF - C100 = checking for answer 1's in
Column 2
cell.FormatConditions(cnt + 1).Interior.ColorIndex = 4 'Colour 2nd
column if 1 in it
Next
End Sub


--
Regards,
Tom Ogilvy

"Trevor" wrote in message
...
Hi, I have a large matrix ( 1000x50) of test groups against test types
where
the grid contains imported test results (eg 1=Not Tested, 2=Passed, 3=Fail
ed
etc - it's more extensive than this but you see what I mean). I want to
easily relate specific test results to groups and types. I have a
supplementary grid beside my grid checking for the existance of the test
results (1's, 2's etc) in the rows and another below my grid checking for
the
existance of the test results (1's, 2's etc) in the columns. I want to
colour
all the rows AND all the columns where test results (say 1's - Not
Tested's)
occur. The trouble is, I can do all the checks OK but can only colour rows
OR
columns, but not both at the same time. The following is a copy of 2 of my
macro codes (for a simplified (5x5 matrix)) doing horizontal and vertical
checks and colouring for the number 1. If I combine them the vertical
colouring erases the horizontal colouring, so I currently have the macro's
assigned to seperate control buttons and only get half the picture. I have
a
macro that identifies all the solitary appearances of (say) 1's, but I
need a
macro that combines the following 2 macros without it erasing the
colouring
it has done in the early part of the macro. Once I crack this for the 5x5
matrix I'm sure I can expand it for my actual matrix.

My 2 macros a-

Sub HorizontalCheck1()
'
' HorizontalCheck1 Macro
Range("B2:F2").Select 'Select whole of first row
Selection.FormatConditions.Delete 'Delete formatting in first row
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=COUNTIF($AA$2,""yes"")" 'CF - AA2 = checking for answer 1's in
row 1
Selection.FormatConditions(1).Interior.ColorIndex = 4 'Colour first
row
if 1 in it

Range("B3:F3").Select 'Select whole of 2nd row
Selection.FormatConditions.Delete 'Delete formatting in 2nd row
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=COUNTIF($AA$3,""yes"")" 'CF - AA3 = checking for answer 1's in
row 2
Selection.FormatConditions(1).Interior.ColorIndex = 4 'Colour 2nd row
if
1 in it
End Sub
Sub VerticalCheck1()
'
' VerticalCheck1 Macro
Range("B2:B5").Select 'Select whole of first column
Selection.FormatConditions.Delete 'Delete formatting in first column
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=COUNTIF($B$100,""yes"")" 'CF - B100 = checking for answer 1's in
column 1
Selection.FormatConditions(1).Interior.ColorIndex = 4 'Colour first
column if 1 in it

Range("C2:C5").Select 'Select whole of 2nd column
Selection.FormatConditions.Delete 'Delete formatting in 2nd column
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=COUNTIF($C$100,""yes"")" 'CF - C100 = checking for answer 1's in
column 2
Selection.FormatConditions(1).Interior.ColorIndex = 4 'Colour 2nd
column
if 1 in it
End Sub
--
Many TIA and sorry for the long problem description.
Trevor

Macro row and column conditional formating problem
 

Tom, that works a treat and has saved me many hours of playing and
experimenting. Many thanks.
--
Trevor


"Tom Ogilvy" wrote:

Sub HorizontalCheck1()
Range("B2:F6").Select
Selection.FormatConditions.Delete 'Delete formatting in first row
' HorizontalCheck1 Macro
Range("B2:F2").Select 'Select whole of first row
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=COUNTIF($AA$2,""yes"")" 'CF - AA2 = checking for answer 1's in row
1
Selection.FormatConditions(1).Interior.ColorIndex = 4 'Colour first row
if 1 in it

Range("B3:F3").Select 'Select whole of 2nd row
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=COUNTIF($AA$3,""yes"")" 'CF - AA3 = checking for answer 1's in row
2
Selection.FormatConditions(1).Interior.ColorIndex = 4 'Colour 2nd row if
1 in it
End Sub
Sub VerticalCheck1()
Dim cell As Range, cnt As Long
'
' VerticalCheck1 Macro
Range("B2:B5").Select 'Select whole of first column
' Selection.FormatConditions.Delete 'Delete formatting in first column
For Each cell In Selection
cnt = cell.FormatConditions.Count
cell.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=COUNTIF($B$100,""yes"")" 'CF - B100 = checking for answer 1's in
Column 1
cell.FormatConditions(cnt + 1).Interior.ColorIndex = 4 'Colour first
column if 1 in it
Next

Range("C2:C5").Select 'Select whole of 2nd column
For Each cell In Selection
cnt = cell.FormatConditions.Count
' Selection.FormatConditions.Delete 'Delete formatting in 2nd column
cell.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=COUNTIF($C$100,""yes"")" 'CF - C100 = checking for answer 1's in
Column 2
cell.FormatConditions(cnt + 1).Interior.ColorIndex = 4 'Colour 2nd
column if 1 in it
Next
End Sub


--
Regards,
Tom Ogilvy

"Trevor" wrote in message
...
Hi, I have a large matrix ( 1000x50) of test groups against test types
where
the grid contains imported test results (eg 1=Not Tested, 2=Passed, 3=Fail
ed
etc - it's more extensive than this but you see what I mean). I want to
easily relate specific test results to groups and types. I have a
supplementary grid beside my grid checking for the existance of the test
results (1's, 2's etc) in the rows and another below my grid checking for
the
existance of the test results (1's, 2's etc) in the columns. I want to
colour
all the rows AND all the columns where test results (say 1's - Not
Tested's)
occur. The trouble is, I can do all the checks OK but can only colour rows
OR
columns, but not both at the same time. The following is a copy of 2 of my
macro codes (for a simplified (5x5 matrix)) doing horizontal and vertical
checks and colouring for the number 1. If I combine them the vertical
colouring erases the horizontal colouring, so I currently have the macro's
assigned to seperate control buttons and only get half the picture. I have
a
macro that identifies all the solitary appearances of (say) 1's, but I
need a
macro that combines the following 2 macros without it erasing the
colouring
it has done in the early part of the macro. Once I crack this for the 5x5
matrix I'm sure I can expand it for my actual matrix.

My 2 macros a-

Sub HorizontalCheck1()
'
' HorizontalCheck1 Macro
Range("B2:F2").Select 'Select whole of first row
Selection.FormatConditions.Delete 'Delete formatting in first row
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=COUNTIF($AA$2,""yes"")" 'CF - AA2 = checking for answer 1's in
row 1
Selection.FormatConditions(1).Interior.ColorIndex = 4 'Colour first
row
if 1 in it

Range("B3:F3").Select 'Select whole of 2nd row
Selection.FormatConditions.Delete 'Delete formatting in 2nd row
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=COUNTIF($AA$3,""yes"")" 'CF - AA3 = checking for answer 1's in
row 2
Selection.FormatConditions(1).Interior.ColorIndex = 4 'Colour 2nd row
if
1 in it
End Sub
Sub VerticalCheck1()
'
' VerticalCheck1 Macro
Range("B2:B5").Select 'Select whole of first column
Selection.FormatConditions.Delete 'Delete formatting in first column
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=COUNTIF($B$100,""yes"")" 'CF - B100 = checking for answer 1's in
column 1
Selection.FormatConditions(1).Interior.ColorIndex = 4 'Colour first
column if 1 in it

Range("C2:C5").Select 'Select whole of 2nd column
Selection.FormatConditions.Delete 'Delete formatting in 2nd column
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=COUNTIF($C$100,""yes"")" 'CF - C100 = checking for answer 1's in
column 2
Selection.FormatConditions(1).Interior.ColorIndex = 4 'Colour 2nd
column
if 1 in it
End Sub
--
Many TIA and sorry for the long problem description.
Trevor