vba for conditional formatting

[jb]

I have written following VBA code to repaint borders for my table. I have also added small testing code to apply conditional formatting on cell B11 and B12 based on value of B84. If value of B84=1 then I want to fill yellow color in B11 and B12. But it is not working.

Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
ReInstateBorders Range("A7:R16,A18:R23")
Application.ScreenUpdating = True
End Sub

Sub ReInstateBorders(theRange)
For Each are In theRange.Areas
  For rw = 1 To are.Rows.Count - 1 Step 2
    For colm = 1 To are.Columns.Count
      are.Cells(rw, colm).Resize(2).BorderAround xlContinuous
    Next colm
  Next rw
Next are
If Sheets("timetable").Range("B84").Value = 1 Then
    Sheets("timetable").Range("B11").Interior.Color = vbYellow
    Sheets("timetable").Range("B12").Interior.Color = vbYellow
End If
End Sub

If this works, I want to write more conditions like, C11 and C12 will be filled with yellow color if C84=1 and so on upto R11 and R12 will be filled with yellow color if R84=1.

Actually, first I applied conditional formatting on a table without vba. But when I drag cells b11 and b12 (required to grag both together) then with cell value, formula also moves. Also many times the border around combo of two cells also disapper. I do not want formula to be moved also I want border as it is. So I tried vba. I was successful in writing a code to redraw border with vba. So now I want conditional formatting using vba. But no result with this code.

 

[vletm]

jb
Why did You open new case?
Your old case is here and waiting for answers ...https://chandoo.org/forum/threads/conditional-formatting-using-vba-coding.38953/
... or more details from You, jb.

 

[jb]

jb
Why did You open new case?
Your old case is here and waiting for answers ...https://chandoo.org/forum/threads/conditional-formatting-using-vba-coding.38953/
... or more details from You, jb.

Sorry for this but I felt that my old question was little bit lengthy and confusing. So I made it shorter and clear. Also I observerd that many viewd my question but no reply due to confusion. That's the only reason. Sorry for inconvinience.