samedi 28 février 2015

excel pattern fill depending of that 2 cells are equal

I'am a beginner with macros in excel. I'am trying to make a VBA-code that change the pattern fill when 2 other cells are equal to each. I have the follow code:



Private Sub Workbook_Open()
' Macro2 Macro

' If (H5)=(J20) Then
Range("H7").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent2
.TintAndShade = 0.799981688894314
.PatternTintAndShade = 0
End With
If (H5) <> (J20) Then
Range("H7").Select
With Selection.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Range("M20").Select
ActiveWorkbook.Save
End If

End Sub


The first part of the code is working but when you change one of the 2 cells then the pattern is not changed back in no Filling. Can someone tell me what is wrong in the code?


Now is the macro only running when you open the workbook. Is it possible that the macro directly is running when you changed a cell?


Now I write the macro for Cell H7 that compares H5 with J20. I want that the macro H5 compares with J20:J29, is this possible one a "easy" way?


Last question: Is it also possible to use the macro for more cells in the same sheet, for example E5-E7,F5-F7,G5-G7,.....NK5-NK7 with the same kollom to compare (J20:J29)?


All suggestions are welkome.


Yes, its a kind of conditional formatting. But I can't find the right formula/code for conditional formatting a cell by compare 2 other cells.

E.g. "E7 is gray when the date in E5 is equal on the date in J20 or J21 or J22 or ... or J29 otherwise E7 is not filled" and

"F7 is gray when the date in F5 is equal on the date in J20 or J21 or J22 or ... or J29 otherwise f7 is not filled" and that so on up to "NK7 is gray when the date in NK5 is equal on the date in J20 or J21 or J22 or ... or J29 otherwise NK7 is not filled".


Aucun commentaire:

Enregistrer un commentaire