Macro to show which cells have validation
Hello!
Anyone know a code to go through the active sheet, look for any cells that have validation, and shade them purple? I am modifying an older workbook, and there are stray cells with no data or formula, just validation, so I am trying to track them down. I guess a "For each ws in Workbook" code would be easier. Thanks for any help! VR/ Lost |
Macro to show which cells have validation
hi, !
Anyone know a code to go through the active sheet, look for any cells that have validation, and shade them purple? I am modifying an olderworkbook, and there are stray cells with no data or formula, just validation, so I am trying to track them down. I guess a "For each ws in Workbook" code would be easier... (i.e.) Sub Paint_DV() Dim wS As Worksheet For Each wS In Worksheets On Error Resume Next wS.Cells.SpecialCells(xlCellTypeAllValidation).Int erior.ColorIndex = 18 Next End Sub hth, hector. |
Macro to show which cells have validation
You should be able to do it with this single line of code (just change my
example worksheet reference of Sheet3 to your own worksheet's name)... Worksheets("Sheet3").UsedRange.SpecialCells(xlCell TypeAllValidation). _ SpecialCells(xlCellTypeBlanks).Interior.ColorIndex = 18 -- Rick (MVP - Excel) "Lostguy" wrote in message ... Hello! Anyone know a code to go through the active sheet, look for any cells that have validation, and shade them purple? I am modifying an older workbook, and there are stray cells with no data or formula, just validation, so I am trying to track them down. I guess a "For each ws in Workbook" code would be easier. Thanks for any help! VR/ Lost |
Macro to show which cells have validation
One line should do the trick. Try this...
Sub ColorValidationCells() Cells.SpecialCells(xlCellTypeAllValidation).Interi or.ColorIndex = 39 End Sub BTW, you actually don't need code to do this. It's in the native commands of Excel. Edit Go To Special Data Validation OK. That selects the cells, then just paint em any color. -Melina On Sep 8, 9:29*pm, Lostguy wrote: Hello! Anyone know a code to go through the active sheet, look for any cells that have validation, and shade them purple? I am modifying an older workbook, and there are stray cells with no data or formula, just validation, so I am trying to track them down. I guess a "For each ws in Workbook" code would be easier. Thanks for any help! VR/ Lost |
Macro to show which cells have validation
By way of clarification... I just looked at Melina's posting which does what
your opening sentence says and locates *all* cells with validation (whether they have anything in them or not) and colors them purple. I picked up on what you said later on when you mentioned "stray cells" that have nothing in them except validation... that is what my code locates (it preserves any cells with something in that along with their validation). Of course, if all the cells are empty, my code and Melina's code will locate the same set of cells. So, which you should use kind of depends on what you actually meant. -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... You should be able to do it with this single line of code (just change my example worksheet reference of Sheet3 to your own worksheet's name)... Worksheets("Sheet3").UsedRange.SpecialCells(xlCell TypeAllValidation). _ SpecialCells(xlCellTypeBlanks).Interior.ColorIndex = 18 -- Rick (MVP - Excel) "Lostguy" wrote in message ... Hello! Anyone know a code to go through the active sheet, look for any cells that have validation, and shade them purple? I am modifying an older workbook, and there are stray cells with no data or formula, just validation, so I am trying to track them down. I guess a "For each ws in Workbook" code would be easier. Thanks for any help! VR/ Lost |
Macro to show which cells have validation
also, is some validation cells are empty, Melina's suggestion could add a second procedure
(but this time) choosing "empty cells" prior to apply any color of course, this shall be done one by one, or... combine the proposals with code and a looping (as per the last paragraph in OP) (the on error resume next line is... "just in case" no validation cells are in a worksheet) ;) regards, hector. Rick Rothstein wrote in message ... By way of clarification... I just looked at Melina's posting which does what your opening sentence says and locates *all* cells with validation (whether they have anything in them or not) and colors them purple. I picked up on what you said later on when you mentioned "stray cells" that have nothing in them except validation... that is what my code locates (it preserves any cells with something in that along with their validation). Of course, if all the cells are empty, my code and Melina's code will locate the same set of cells. So, which you should use kind of depends on what you actually meant. -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... You should be able to do it with this single line of code (just change my example worksheet reference of Sheet3 to your own worksheet's name)... Worksheets("Sheet3").UsedRange.SpecialCells(xlCell TypeAllValidation). _ SpecialCells(xlCellTypeBlanks).Interior.ColorIndex = 18 -- Rick (MVP - Excel) "Lostguy" wrote in message ... Hello! Anyone know a code to go through the active sheet, look for any cells that have validation, and shade them purple? I am modifying an older workbook, and there are stray cells with no data or formula, just validation, so I am trying to track them down. I guess a "For each ws in Workbook" code would be easier. Thanks for any help! VR/ Lost |
Macro to show which cells have validation
Oops. I didn't catch the part about validated cells being blank. I
thought that was too easy. <g But on this topic, Rick, what if the OP had blank validated cells outside the UsedRange, which is in an area xlCellTypeBlanks doesn't seem to evaluate but xlCellTypeValidation does. Without both working outside the UsedRange, it's no good to catch those cells. The user could do a xlCellTypeValidation selection and then loop through the selection finding blank cells. That makes for a smaller loop, but is there a loopless way? Is there a way to force xlCellTypeBlanks to evaluate all selected cells, not just the UsedRange? You know... in case I'm asked this on a game show. <lol -Melina On Sep 8, 9:59*pm, "Rick Rothstein" wrote: You should be able to do it with this single line of code (just change my example worksheet reference of Sheet3 to your own worksheet's name)... Worksheets("Sheet3").UsedRange.SpecialCells(xlCell TypeAllValidation). _ * * * * * * *SpecialCells(xlCellTypeBlanks).Interior.ColorInde x = 18 -- Rick (MVP - Excel) "Lostguy" wrote in message ... Hello! Anyone know a code to go through the active sheet, look for any cells that have validation, and shade them purple? I am modifying an older workbook, and there are stray cells with no data or formula, just validation, so I am trying to track them down. I guess a "For each ws in Workbook" code would be easier. Thanks for any help! VR/ Lost |
Macro to show which cells have validation
Good catch! I completely forgot that Validations can lie outside of the
UsedRange. Okay, to solve this problem, I created an UnusedRange function to get around fact that SpecialCells(xlCellTypeBlanks) won't look outside of the UsedRange. Below is my coded solution. The OP should copy all of this into a Module (Insert/Module from the VB editor's menu bar) and run the DeleteUnusedValidations macro. Sub DeleteUnusedValidations() On Error Resume Next Worksheets("Sheet3").UsedRange.SpecialCells(xlCell TypeAllValidation). _ SpecialCells(xlCellTypeBlanks).Interior.ColorIndex = 18 UnusedRange("Sheet3").SpecialCells(xlCellTypeAllVa lidation). _ Interior.ColorIndex = 18 End Sub Function UnusedRange(WorksheetName As String) As Range Dim UR As Range Dim WS As Worksheet Set WS = Worksheets(WorksheetName) Set UR = WS.UsedRange With UR With .Offset(.Rows.Count, .Columns.Count) Set UnusedRange = .Resize(1, WS.Columns.Count - _ .Column + 1).EntireColumn Set UnusedRange = Union(UnusedRange, .Resize(WS.Rows.Count - _ .Row + 1, 1).EntireRow) End With If UR.Row 1 Then Set UnusedRange = Union(UnusedRange, WS.Range("A1", _ WS.Range(Split(.Offset(-1).Address, _ ":")(0))).EntireRow) End If If UR.Column 1 Then Set UnusedRange = Union(UnusedRange, WS.Range("A1", _ WS.Range(Split(.Offset(, -1).Address, _ ":")(0))).EntireColumn) End If End With End Function -- Rick (MVP - Excel) "Mel" wrote in message ... Oops. I didn't catch the part about validated cells being blank. I thought that was too easy. <g But on this topic, Rick, what if the OP had blank validated cells outside the UsedRange, which is in an area xlCellTypeBlanks doesn't seem to evaluate but xlCellTypeValidation does. Without both working outside the UsedRange, it's no good to catch those cells. The user could do a xlCellTypeValidation selection and then loop through the selection finding blank cells. That makes for a smaller loop, but is there a loopless way? Is there a way to force xlCellTypeBlanks to evaluate all selected cells, not just the UsedRange? You know... in case I'm asked this on a game show. <lol -Melina On Sep 8, 9:59 pm, "Rick Rothstein" wrote: You should be able to do it with this single line of code (just change my example worksheet reference of Sheet3 to your own worksheet's name)... Worksheets("Sheet3").UsedRange.SpecialCells(xlCell TypeAllValidation). _ SpecialCells(xlCellTypeBlanks).Interior.ColorIndex = 18 -- Rick (MVP - Excel) "Lostguy" wrote in message ... Hello! Anyone know a code to go through the active sheet, look for any cells that have validation, and shade them purple? I am modifying an older workbook, and there are stray cells with no data or formula, just validation, so I am trying to track them down. I guess a "For each ws in Workbook" code would be easier. Thanks for any help! VR/ Lost |
Macro to show which cells have validation
In case you missed my correction in my response to Mel (Melina)...
Copy/Paste the following into a Module (Insert/Module from the VB editor's menu bar) and then run the ColorUnusedValidations macro part of it... Sub ColorUnusedValidations() On Error Resume Next Worksheets("Sheet3").UsedRange.SpecialCells(xlCell TypeAllValidation). _ SpecialCells(xlCellTypeBlanks).Interior.ColorIndex = 18 UnusedRange("Sheet3").SpecialCells(xlCellTypeAllVa lidation). _ Interior.ColorIndex = 18 End Sub Function UnusedRange(WorksheetName As String) As Range Dim UR As Range Dim WS As Worksheet Set WS = Worksheets(WorksheetName) Set UR = WS.UsedRange With UR With .Offset(.Rows.Count, .Columns.Count) Set UnusedRange = .Resize(1, WS.Columns.Count - _ .Column + 1).EntireColumn Set UnusedRange = Union(UnusedRange, .Resize(WS.Rows.Count - _ .Row + 1, 1).EntireRow) End With If UR.Row 1 Then Set UnusedRange = Union(UnusedRange, WS.Range("A1", _ WS.Range(Split(.Offset(-1).Address, _ ":")(0))).EntireRow) End If If UR.Column 1 Then Set UnusedRange = Union(UnusedRange, WS.Range("A1", _ WS.Range(Split(.Offset(, -1).Address, _ ":")(0))).EntireColumn) End If End With End Function -- Rick (MVP - Excel) "Lostguy" wrote in message ... Hello! Anyone know a code to go through the active sheet, look for any cells that have validation, and shade them purple? I am modifying an older workbook, and there are stray cells with no data or formula, just validation, so I am trying to track them down. I guess a "For each ws in Workbook" code would be easier. Thanks for any help! VR/ Lost |
Macro to show which cells have validation
The DeleteUnusedValidations macro is incorrectly named... it should be named
ColorUnusedValidations (we are coloring the cells, not deleting the validations). -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... Good catch! I completely forgot that Validations can lie outside of the UsedRange. Okay, to solve this problem, I created an UnusedRange function to get around fact that SpecialCells(xlCellTypeBlanks) won't look outside of the UsedRange. Below is my coded solution. The OP should copy all of this into a Module (Insert/Module from the VB editor's menu bar) and run the DeleteUnusedValidations macro. Sub DeleteUnusedValidations() On Error Resume Next Worksheets("Sheet3").UsedRange.SpecialCells(xlCell TypeAllValidation). _ SpecialCells(xlCellTypeBlanks).Interior.ColorIndex = 18 UnusedRange("Sheet3").SpecialCells(xlCellTypeAllVa lidation). _ Interior.ColorIndex = 18 End Sub Function UnusedRange(WorksheetName As String) As Range Dim UR As Range Dim WS As Worksheet Set WS = Worksheets(WorksheetName) Set UR = WS.UsedRange With UR With .Offset(.Rows.Count, .Columns.Count) Set UnusedRange = .Resize(1, WS.Columns.Count - _ .Column + 1).EntireColumn Set UnusedRange = Union(UnusedRange, .Resize(WS.Rows.Count - _ .Row + 1, 1).EntireRow) End With If UR.Row 1 Then Set UnusedRange = Union(UnusedRange, WS.Range("A1", _ WS.Range(Split(.Offset(-1).Address, _ ":")(0))).EntireRow) End If If UR.Column 1 Then Set UnusedRange = Union(UnusedRange, WS.Range("A1", _ WS.Range(Split(.Offset(, -1).Address, _ ":")(0))).EntireColumn) End If End With End Function -- Rick (MVP - Excel) "Mel" wrote in message ... Oops. I didn't catch the part about validated cells being blank. I thought that was too easy. <g But on this topic, Rick, what if the OP had blank validated cells outside the UsedRange, which is in an area xlCellTypeBlanks doesn't seem to evaluate but xlCellTypeValidation does. Without both working outside the UsedRange, it's no good to catch those cells. The user could do a xlCellTypeValidation selection and then loop through the selection finding blank cells. That makes for a smaller loop, but is there a loopless way? Is there a way to force xlCellTypeBlanks to evaluate all selected cells, not just the UsedRange? You know... in case I'm asked this on a game show. <lol -Melina On Sep 8, 9:59 pm, "Rick Rothstein" wrote: You should be able to do it with this single line of code (just change my example worksheet reference of Sheet3 to your own worksheet's name)... Worksheets("Sheet3").UsedRange.SpecialCells(xlCell TypeAllValidation). _ SpecialCells(xlCellTypeBlanks).Interior.ColorIndex = 18 -- Rick (MVP - Excel) "Lostguy" wrote in message ... Hello! Anyone know a code to go through the active sheet, look for any cells that have validation, and shade them purple? I am modifying an older workbook, and there are stray cells with no data or formula, just validation, so I am trying to track them down. I guess a "For each ws in Workbook" code would be easier. Thanks for any help! VR/ Lost |
All times are GMT +1. The time now is 12:04 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com