Forum Discussion
Solved
Is there a way to set a static range for conditional formats on Excel for Web?
As the question states, I have a fair amount of conditional formatting available in a workbook and I want to set the range the conditional formats apply to so that the only way to change that range is to manually edit the range in the conditional formatting menu. Is there a way to do this, currently?
Right now, when anyone who has access the sheet moves data around or copy/cuts/pastes things around (which is fairly often) it also edits the range and I do not want this to happen.
Using a named range isn't possible, because the formats do not allow the use of a named range when choosing where the formats are active.
To clarify, the highlighted section in the picture is the range selection I am talking about:
I do not want to edit the formula used, as it functions exactly the way the team need it to.
As far as I'm aware, Excel for the web doesn't provide a built-in feature to set a static range for conditional formats. If you move or change data within the range, the range will automatically adjust to the conditional formatting rules.
One possible workaround you can try is to use a VBA (Visual Basic for Applications) macro to apply and maintain the conditional formatting with a static range. However, please note that VBA macros are not supported in Excel for Web. They can only be used in the desktop version of Excel.
If you need to maintain a static range for conditional formatting in Excel for Web, you may consider using the desktop version of Excel. The desktop version offers more advanced features and customization options compared to the web version.
- Exxcel 365 version April 2023 contains positive changes in how conditional formatting ranges are managed. Maybe that update will give you such possibility.
https://techcommunity.microsoft.com/t5/excel-blog/what-s-new-in-excel-april-2023/ba-p/3791667 As far as I'm aware, Excel for the web doesn't provide a built-in feature to set a static range for conditional formats. If you move or change data within the range, the range will automatically adjust to the conditional formatting rules.
One possible workaround you can try is to use a VBA (Visual Basic for Applications) macro to apply and maintain the conditional formatting with a static range. However, please note that VBA macros are not supported in Excel for Web. They can only be used in the desktop version of Excel.
If you need to maintain a static range for conditional formatting in Excel for Web, you may consider using the desktop version of Excel. The desktop version offers more advanced features and customization options compared to the web version.
Thanks for the response. Unfortunately, the workbook was migrated to the web so we could maintain a live updated version, and we have not been able to do this with the desktop version.
Seems like I'll have to go test out Google Sheets.
