RHUL
06-18-2008, 08:01 AM
Hello all,
I’m looking for some advice, suggestions or examples on how to proceed with this problem.
I need a column on a sheet to display the number of values in the source row that exceed a value in Column B of that row on another sheet.
Highlighting these values occurs using the following macro:
'----------
Sub ViewData1()
Sheets("Sheet2").Select
Range("B2").Select
LastRow = Range("C" & Rows.Count).End(xlUp).Row
With Range("C3:IV" & LastRow).FormatConditions
.Delete
.Add Type:=xlCellValue, Operator:=xlGreaterEqual, Formula1:="=RC2"
.Item(1).Interior.ColorIndex = 40
End With
End Sub
'----------
I would like to have these highlighted cells counted for each row and the result displayed in a table on another sheet.
I have attached a (very) simplified version of my spreadsheet to demonstrate what I’m trying to achieve.
This does differ from my spreadsheet in that mine has a front page with all the navigation buttons on, the only button on the other sheets takes you back to the ‘front page’. This means there is only one button taking you to the ‘Total Exceedances’ sheet.
The way I see it, I have two options:
Option 1:
Count all highlighted cells in row on sheet using a formula in the relevant column in the ‘totals’ sheet, which is linked to a function.
Pros and Cons: Won’t automatically update when source numbers change, but should be faster?
Option 2:
Write a macro to count cells in rows which are greater than or equal to and paste result into relevant row in table when navigating to the ‘totals’ sheet using a button.
Pros and Cons: Should auto-update (on button click) when source numbers change, but may be slow?
I’m erring towards Option 2 at the moment (as it relies on the source data rather than the formatting), but bear in mind that I have 3-6 sheets which contain the highlighted cells so don’t really want 3-6 different macros, which are all essentially doing the same thing…!
Any help (no matter how small) is appreciated!
.
~Oorang
Thanks,
I’m looking for some advice, suggestions or examples on how to proceed with this problem.
I need a column on a sheet to display the number of values in the source row that exceed a value in Column B of that row on another sheet.
Highlighting these values occurs using the following macro:
'----------
Sub ViewData1()
Sheets("Sheet2").Select
Range("B2").Select
LastRow = Range("C" & Rows.Count).End(xlUp).Row
With Range("C3:IV" & LastRow).FormatConditions
.Delete
.Add Type:=xlCellValue, Operator:=xlGreaterEqual, Formula1:="=RC2"
.Item(1).Interior.ColorIndex = 40
End With
End Sub
'----------
I would like to have these highlighted cells counted for each row and the result displayed in a table on another sheet.
I have attached a (very) simplified version of my spreadsheet to demonstrate what I’m trying to achieve.
This does differ from my spreadsheet in that mine has a front page with all the navigation buttons on, the only button on the other sheets takes you back to the ‘front page’. This means there is only one button taking you to the ‘Total Exceedances’ sheet.
The way I see it, I have two options:
Option 1:
Count all highlighted cells in row on sheet using a formula in the relevant column in the ‘totals’ sheet, which is linked to a function.
Pros and Cons: Won’t automatically update when source numbers change, but should be faster?
Option 2:
Write a macro to count cells in rows which are greater than or equal to and paste result into relevant row in table when navigating to the ‘totals’ sheet using a button.
Pros and Cons: Should auto-update (on button click) when source numbers change, but may be slow?
I’m erring towards Option 2 at the moment (as it relies on the source data rather than the formatting), but bear in mind that I have 3-6 sheets which contain the highlighted cells so don’t really want 3-6 different macros, which are all essentially doing the same thing…!
Any help (no matter how small) is appreciated!
.
~Oorang
Thanks,