He has cells that contain both formulas and conditional formatting. Load multiple Excel (*.xlsx, *.Larry wrote about a problem he was encountering with protecting a worksheet he developed.Load Excel data table to a Python pandas dataframe.Get member details from an Outlook distribution list with Python.Excel Formula: Find overlapping date ranges.Python pandas: lookup value for dates from date ranges.VBA function to check if cell has data validation VBA function to check if a name refers to a range HasSheetAn圜onditionalFormatting.xlsm Tested: Simply click one of the ‘ Initiate Test on ThisWorkbook.Sheets‘ button. One of them has conditional formatting, the other one does not have any. In the example file there are two sheets. If you have met any other way to check if a sheet has any conditional formatting, do not be shy and share :). If sh. > 0 Thenį_sheetHasConditionalFormattingCount = True End If End Function 'Ig greater than 0 sheet has conditional formatting ![]() ' Purpose: Examina if a sheet has conditional formatting using FormatConditions collection Count propertyįunction f_sheetHasConditionalFormattingCount(sh As Worksheet) As Boolean 'Count the number of objects in Formatconditions collection If the number of objects in the FormatConditions collection is 0, it returns FALSE, otherwise TRUE. The function also only requires a sheet as an input and it is also a boolean function. On Error GoTo 0 'If condFormattingRange is Nothing there is no conditional formatting on the sheetį_sheetHasConditionalFormatting = False Elseį_sheetHasConditionalFormatting = True Set condFormattingRange = Nothing End If End Function Function using FormatConditions.Count property On Error Resume Next Set condFormattingRange = sh.Cells.SpecialCells(xlCellTypeAllFormatConditions) 'the sheet condFormattingRange remains Nothing 'Trying to assign cells with conditional formatting to a range object, if there is no cells with conditional formatting on ' -įunction f_sheetHasConditionalFormatting(sh As Worksheet) As Boolean Dim condFormattingRange As Range ![]() ' Purpose: Function to check if a sheet contains any conditional formatting If the range remains Nothing the return value is False (no formula on the sheet) otherwise it is True. Then it tries to assign the cell(s) with conditional formatting to a range variable, using SpecialCells method. The function requires only one input parameter the sheet you would like to check. It is easy to perform a pre-check to prevent this kind of error. In VBA same issue could occur, for example trying to list all the conditional formatting in a workbook, but one of the sheets has got any. MACRO CONDITIONAL FORMATTING EXCEL 2016 HOW TOThe SpecialCells solution is pretty much identical with how to check if a sheet has any formula or has any data validation cell. In this case you only have to use xlCellTypeAllFormatConditions as the SpecialCells parameter.Īs a reminder when you use the “Go To Special” /(Ctrl+G) -> Special/ feature and there is no cell for the chosen special option you will receive a “No cells were found”. In order to check if a sheet has any conditional formatting or not, you can SpecialCells method or the FormatConditions collection Count property(much shorter □ ).
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |