0 down vote favorite | i want to make a macro that by any change in cells run automatically, so i write below code for my sheet <code> Private Sub Worksheet_Change(ByVal Target As Range) Dim KeyCells As Range Set KeyCells = Range("B1:U1") If Not Application.Intersect(KeyCells, Range(Target.Address)) _ Is Nothing Then Call changecolor End If End Sub</code> any change for range B1 to U1 call changecolor, and changecolor code is: <code>Sub changecolor() Dim sum As Integer For i = 0 To 19 ColorVar = Cells(1, i + 2).Interior.Color If Not ColorVar = RGB(255, 255, 255) Then sum = sum + 1 End If Next i Cells(1, 1).Value = sum * 5 End Sub</code> in above code any change color for range B1 to U1 should compute a simple calculation and show result in A1 but when i test my code and i change color of some of cells i can't get any result and it seems code is not sensitive about color change of cells how can i resole my problem? or is there any other code to get my desire? |
<tbody>
</tbody>