提取"祁阳"工作簿里"录入正表"工作表的所有有效性和条件格式为VBA代码
时间: 2023-09-17 18:07:58 浏览: 42
以下是提取"祁阳"工作簿里"录入正表"工作表的所有有效性和条件格式的VBA代码:
```
Sub ExtractValidationAndConditionalFormatting()
Dim ws As Worksheet
Dim validationFormula As String
Dim conditionalFormula As String
Dim validationRange As Range
Dim conditionalRange As Range
'Set the worksheet to "录入正表"
Set ws = ThisWorkbook.Sheets("录入正表")
'Loop through all cells in the worksheet
For Each cell In ws.Cells
'Check if the cell has any data validation
If Not cell.Validation Is Nothing Then
validationFormula = cell.Validation.Formula1
Set validationRange = cell
'Loop through all cells in the validation range
For Each valCell In cell.Validation.Formula1Range.Cells
'Combine the validation formulas for all cells in the validation range
If valCell.Address <> cell.Address Then
validationFormula = validationFormula & "," & valCell.Address
Set validationRange = Union(validationRange, valCell)
End If
Next valCell
'Print the validation formula and range to Immediate Window
Debug.Print "Validation: " & validationFormula
Debug.Print "Validation Range: " & validationRange.Address
End If
'Check if the cell has any conditional formatting
If Not cell.FormatConditions Is Nothing Then
'Loop through all conditional formatting rules for the cell
For Each condFormat In cell.FormatConditions
conditionalFormula = condFormat.Formula1
Set conditionalRange = cell
'Loop through all cells in the conditional formatting range
For Each condCell In condFormat.AppliesTo.Cells
'Combine the conditional formatting formulas for all cells in the range
If condCell.Address <> cell.Address Then
conditionalFormula = conditionalFormula & "," & condCell.Address
Set conditionalRange = Union(conditionalRange, condCell)
End If
Next condCell
'Print the conditional formatting formula and range to Immediate Window
Debug.Print "Conditional Formatting: " & conditionalFormula
Debug.Print "Conditional Formatting Range: " & conditionalRange.Address
Next condFormat
End If
Next cell
End Sub
```
该代码将输出所有有效性和条件格式的公式以及应用它们的单元格范围。请注意,对于数据验证,代码将合并单元格范围中的所有公式。