CODEWITHSUNDEEP
excelvba
user1495475
user1495475

Reputation: 1057

VBA:Lock and fill color for selected cells in excel

Following is my vba code,where I fill data to the cells,all I have to do is to lock the same cells and put some color for those.

//VBA:-
    ActiveWorkbook.Names("book.a.").RefersToRange = doc.getAttribute("a")
    ActiveWorkbook.Names("book.b.").RefersToRange = doc.getAttribute("b")
    ActiveWorkbook.Names("book.c.").RefersToRange = doc.getAttribute("c")
    ActiveWorkbook.Names("book.d.").RefersToRange =doc.getAttribute("d")

How to do this?I got to do this to many more cells. What do you suggest? Thanks in advance.

Upvotes: 0

Views: 758

Answers (1)

MattCrum
MattCrum

Reputation: 1110

How many cells? Are they contiguous? If so, we can loop through the range rather than referring to the named ranges. If not, we need to build an array of the names somehow - are they all in the form "book.a."?

This does what you want for one of the cells (you'll need to unprotect the worksheet before unlocking / amending cells, then protect it again when done):

Dim strName As String, objName As Name, strID As String

Sheet1.Unprotect
For Each objName In ActiveWorkbook.Names
    strName = objName.Name
    If InStr(1, strName, "book", vbTextCompare) > 0 Then
        strID = Mid(strName, InStr(1, strName, ".", vbTextCompare) + 1, 1)
        With ActiveWorkbook.Names(strName).RefersToRange
            .Value = doc.getAttribute(strID)
            .Interior.Color = vbRed
            .Locked = True
        End With
    End If
Next
Sheet1.Protect

Upvotes: 1

Related Questions