| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Summary based on highlighted cells
I have asked this before, but got no answer - just more questions. I have
modified my request. I need to create a macro to add the following numbers in cell A based on highlighted cells in that row: Add 17 in cell A if cell G is highlighted Add 10 in cell A for each cell from H thru P that is highlighted Add 1 in cell A for each cell from Q thru AA that is highlighted Add 11 in cell A if cell AB is highlighted Add 1 in cell A for each cell from AC thru AE that is highlighted I need to do this for every row of my spreadsheet. By highlight I mean any color except for white (no color). This macro should be executed by me whenever all updates are in and I need totals. Thank you. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Summary based on highlighted cells
This macro checks for any type of highlighting (note that formatting a cell
with white background is different than "no fill") and calculated based on the values you gave. Change the one "For" line values as appropriate for your spreadsheet. Sub HighlightCheck() Dim xValue As Integer Dim i As Integer 'Change these to your upper and 'lower boundaries for rows For i = 2 To 10 xValue = 0 If Range("G" & i).Interior.ColorIndex < xlNone Then xValue = xValue + 17 End If For Each cell In Range("H" & i & ":P" & i) If cell.Interior.ColorIndex < xlNone Then xValue = xValue + 10 End If Next cell For Each cell In Range("Q" & i & ":AA" & i) If cell.Interior.ColorIndex < xlNone Then xValue = xValue + 1 End If Next cell If Range("AB" & i).Interior.ColorIndex < xlNone Then xValue = xValue + 11 End If For Each cell In Range("AC" & i & ":AE" & i) If cell.Interior.ColorIndex < xlNone Then xValue = xValue + 1 End If Next cell Range("A" & i).Value = xValue Next End Sub -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Vic" wrote: I have asked this before, but got no answer - just more questions. I have modified my request. I need to create a macro to add the following numbers in cell A based on highlighted cells in that row: Add 17 in cell A if cell G is highlighted Add 10 in cell A for each cell from H thru P that is highlighted Add 1 in cell A for each cell from Q thru AA that is highlighted Add 11 in cell A if cell AB is highlighted Add 1 in cell A for each cell from AC thru AE that is highlighted I need to do this for every row of my spreadsheet. By highlight I mean any color except for white (no color). This macro should be executed by me whenever all updates are in and I need totals. Thank you. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Summary based on highlighted cells
I should also mention that conditional formats are not detected either. If
you're using conditional formats though, you could build a formula based on those, so this shouldn't be a problem. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Luke M" wrote: This macro checks for any type of highlighting (note that formatting a cell with white background is different than "no fill") and calculated based on the values you gave. Change the one "For" line values as appropriate for your spreadsheet. Sub HighlightCheck() Dim xValue As Integer Dim i As Integer 'Change these to your upper and 'lower boundaries for rows For i = 2 To 10 xValue = 0 If Range("G" & i).Interior.ColorIndex < xlNone Then xValue = xValue + 17 End If For Each cell In Range("H" & i & ":P" & i) If cell.Interior.ColorIndex < xlNone Then xValue = xValue + 10 End If Next cell For Each cell In Range("Q" & i & ":AA" & i) If cell.Interior.ColorIndex < xlNone Then xValue = xValue + 1 End If Next cell If Range("AB" & i).Interior.ColorIndex < xlNone Then xValue = xValue + 11 End If For Each cell In Range("AC" & i & ":AE" & i) If cell.Interior.ColorIndex < xlNone Then xValue = xValue + 1 End If Next cell Range("A" & i).Value = xValue Next End Sub -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Vic" wrote: I have asked this before, but got no answer - just more questions. I have modified my request. I need to create a macro to add the following numbers in cell A based on highlighted cells in that row: Add 17 in cell A if cell G is highlighted Add 10 in cell A for each cell from H thru P that is highlighted Add 1 in cell A for each cell from Q thru AA that is highlighted Add 11 in cell A if cell AB is highlighted Add 1 in cell A for each cell from AC thru AE that is highlighted I need to do this for every row of my spreadsheet. By highlight I mean any color except for white (no color). This macro should be executed by me whenever all updates are in and I need totals. Thank you. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Summary based on highlighted cells
Thank you Luke,
How can I change to check for yellow and red highlighted cell only? Apparently, there was a grey color that should be OK and I should not count this in. Each cell represents a visit with the visit date or "&" for scheduled visits with no dates. All other potential visits are blank and grey. All visits with missing documentation are colored in yellow. All visits with bad documentation are colored in red. I need to count missing and bad documentation. Depending on the visit type it could be 1 or 10 or 11 or 17 pages. Thank you. "Luke M" wrote: I should also mention that conditional formats are not detected either. If you're using conditional formats though, you could build a formula based on those, so this shouldn't be a problem. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Luke M" wrote: This macro checks for any type of highlighting (note that formatting a cell with white background is different than "no fill") and calculated based on the values you gave. Change the one "For" line values as appropriate for your spreadsheet. Sub HighlightCheck() Dim xValue As Integer Dim i As Integer 'Change these to your upper and 'lower boundaries for rows For i = 2 To 10 xValue = 0 If Range("G" & i).Interior.ColorIndex < xlNone Then xValue = xValue + 17 End If For Each cell In Range("H" & i & ":P" & i) If cell.Interior.ColorIndex < xlNone Then xValue = xValue + 10 End If Next cell For Each cell In Range("Q" & i & ":AA" & i) If cell.Interior.ColorIndex < xlNone Then xValue = xValue + 1 End If Next cell If Range("AB" & i).Interior.ColorIndex < xlNone Then xValue = xValue + 11 End If For Each cell In Range("AC" & i & ":AE" & i) If cell.Interior.ColorIndex < xlNone Then xValue = xValue + 1 End If Next cell Range("A" & i).Value = xValue Next End Sub -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Vic" wrote: I have asked this before, but got no answer - just more questions. I have modified my request. I need to create a macro to add the following numbers in cell A based on highlighted cells in that row: Add 17 in cell A if cell G is highlighted Add 10 in cell A for each cell from H thru P that is highlighted Add 1 in cell A for each cell from Q thru AA that is highlighted Add 11 in cell A if cell AB is highlighted Add 1 in cell A for each cell from AC thru AE that is highlighted I need to do this for every row of my spreadsheet. By highlight I mean any color except for white (no color). This macro should be executed by me whenever all updates are in and I need totals. Thank you. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Summary based on highlighted cells
We can change the macro to search for color indexex 3 and 6 (red and yellow
respectively). Sub HighlightCheck() Dim xValue As Integer Dim i As Integer 'Change these to your upper and 'lower boundaries for rows For i = 2 To 10 xValue = 0 'Yellow = 6 'Red = 3 If Range("G" & i).Interior.ColorIndex = 6 Or _ Range("G" & i).Interior.ColorIndex = 3 Then xValue = xValue + 17 End If For Each cell In Range("H" & i & ":P" & i) If cell.Interior.ColorIndex = 6 Or _ cell.Interior.ColorIndex = 3 Then xValue = xValue + 10 End If Next cell For Each cell In Range("Q" & i & ":AA" & i) If cell.Interior.ColorIndex = 6 Or _ cell.Interior.ColorIndex = 3 Then xValue = xValue + 1 End If Next cell If Range("AB" & i).Interior.ColorIndex = 6 Or _ Range("AB" & i).Interior.ColorIndex = 3 Then xValue = xValue + 11 End If For Each cell In Range("AC" & i & ":AE" & i) If cell.Interior.ColorIndex = 6 Or _ cell.Interior.ColorIndex = 3 Then xValue = xValue + 1 End If Next cell Range("A" & i).Value = xValue Next End Sub -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Vic" wrote: Thank you Luke, How can I change to check for yellow and red highlighted cell only? Apparently, there was a grey color that should be OK and I should not count this in. Each cell represents a visit with the visit date or "&" for scheduled visits with no dates. All other potential visits are blank and grey. All visits with missing documentation are colored in yellow. All visits with bad documentation are colored in red. I need to count missing and bad documentation. Depending on the visit type it could be 1 or 10 or 11 or 17 pages. Thank you. "Luke M" wrote: I should also mention that conditional formats are not detected either. If you're using conditional formats though, you could build a formula based on those, so this shouldn't be a problem. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Luke M" wrote: This macro checks for any type of highlighting (note that formatting a cell with white background is different than "no fill") and calculated based on the values you gave. Change the one "For" line values as appropriate for your spreadsheet. Sub HighlightCheck() Dim xValue As Integer Dim i As Integer 'Change these to your upper and 'lower boundaries for rows For i = 2 To 10 xValue = 0 If Range("G" & i).Interior.ColorIndex < xlNone Then xValue = xValue + 17 End If For Each cell In Range("H" & i & ":P" & i) If cell.Interior.ColorIndex < xlNone Then xValue = xValue + 10 End If Next cell For Each cell In Range("Q" & i & ":AA" & i) If cell.Interior.ColorIndex < xlNone Then xValue = xValue + 1 End If Next cell If Range("AB" & i).Interior.ColorIndex < xlNone Then xValue = xValue + 11 End If For Each cell In Range("AC" & i & ":AE" & i) If cell.Interior.ColorIndex < xlNone Then xValue = xValue + 1 End If Next cell Range("A" & i).Value = xValue Next End Sub -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Vic" wrote: I have asked this before, but got no answer - just more questions. I have modified my request. I need to create a macro to add the following numbers in cell A based on highlighted cells in that row: Add 17 in cell A if cell G is highlighted Add 10 in cell A for each cell from H thru P that is highlighted Add 1 in cell A for each cell from Q thru AA that is highlighted Add 11 in cell A if cell AB is highlighted Add 1 in cell A for each cell from AC thru AE that is highlighted I need to do this for every row of my spreadsheet. By highlight I mean any color except for white (no color). This macro should be executed by me whenever all updates are in and I need totals. Thank you. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Delete Rows Based on highlighted Color | Excel Discussion (Misc queries) | |||
| PivotTables - Calculations based on generated summary data | Excel Discussion (Misc queries) | |||
| Selecting multiple cells, I can't see the cells highlighted | Excel Discussion (Misc queries) | |||
| Summary Based on Multiple Criteria | Excel Worksheet Functions | |||
| Create summary based on specifc value in a different sheet | Excel Worksheet Functions | |||
Linear Mode
