Sunday, February 26, 2017

Why is this macro not working correctly in Excel 2010?




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. :)


Answer




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

No comments:

Post a Comment

hard drive - Leaving bad sectors in unformatted partition?

Laptop was acting really weird, and copy and seek times were really slow, so I decided to scan the hard drive surface. I have a couple hundr...