Search code examples
vbaexcelstylesheet

VBA change cell's value if it has a user defined style

I want to change all cells in Excel spreadsheet of specific, user defined style (let's say 'Beauty' style) to value "Beast".

Sub BulkChangeValeOfStyle() Dim TheCell As Range For Each TheCell In ActiveSheet.UsedRange.Cells If TheCell.Style = "Beauty" Then TheCell.Value = "Beast" End If Next End Sub

This code is too slow. I got lots of those Beauty cells scattered round the spreadsheet. Is it possible to make it like this:

ActiveSheet.AllCellsWithaStyle="Beauty".value="Beast"

Update This is just an idea: ActiveSheet.Cells.SpecialCells(xlCellTypeSameFormatConditions).Activate or alternatively ActiveSheet.Cells.SpecialCells(xlCellTypeSameValidation).Activate

but I do not know how to set up the criteria which determine xlCellTypeSameFormatConditions. Or criteria for xlCellTypeSameValidation. Anybody knows?

Solution

  • I don't think it is possible with a better solution than your For Each loop. But you should create a new Style for the cells you want to modify, and modify the format properties of that style when needed like this: 

    ThisWorkbook.Styles.Item("Good").Interior.ColorIndex=4