Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Well, I'm curious as to why this macro isn't working.

The macro is supposed to work like this. I have a cell with black background and black text. What is supposed to happen is that this cell's background is supposed to turn to white once the macro has been clicked.

When I record this macro, what happens is that when I click this macro, whichever cell(s) has been highlighted will get the white background, instead of that particular cell.

I have about 25 cells I want this to happen to, but I want them to have their own individual macro. So whenever, in the macro code, it needs to mention the cell's name, simply put something like 'C/L' (cell location) or whatever, just to tell me.

All help is appreciated. :)

Here is the faulty macro code that I am using:

Sub CaseOpen1()
'
' CaseOpen1 Macro
'

'
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
End Sub

Once again, all help appreciated. :)

share|improve this question
add comment

1 Answer 1

up vote 1 down vote accepted

The "With Selection" in your code causes it to operate on the highlighted (selected) cell. What you need to do instead is to declare which cell you want the macro to operate on.

Sub CaseOpen1()
'
' CaseOpen1 Macro
'
    Dim rng As Range                   '// declare 'rng' as variable of type Range
    Set rng = ActiveSheet.Range("A1")  '// assign the cell to be acted on to 'rng'
    With rng.Interior                  '// change color of 'rng' rather than Selection
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
End Sub 
share|improve this answer
    
I'm sorry, but can you please post that as a whole macro code? I tried to do place it where I thought it would go, but it had a problem with me. –  Mordecaii Apr 20 '13 at 1:25
    
See amended code which should work for you. –  chuff Apr 20 '13 at 3:34
    
The amended code works like a charm. Thank you so much. :) –  Mordecaii Apr 20 '13 at 3:57
    
Glad it worked out for you! –  chuff Apr 20 '13 at 3:59
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.