G
Guest
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 are:-
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
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 are:-
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