Excel VBA - Change cell colour based on event from other sheets

Question

I am fairly new to Excel and VBA programming. Though I have experience in both visual basic and PHP. So the basics understanding is there.

Wow, this is hard to explain in a good way... This might not be the best way to solve this but this is how I have solved it right now.

A total of 3 sheets. sheet1 - A summary of other sheets. Sheet2, some data and user input. Sheet3 - Data.

The user inputs the length of a steel bar in sheet2. The user also adds other values that adds weight on the steel bar. in sheet 1 a summary of the loads are calculated. one of the cells shows only the load on the bar. Sheet3 have the maximum loading info pr. length of steel bar. I want the summary cell in sheet1 to be red of the value is greater than the maximum loading in sheet3.

So I want a macro to run every time the cell in sheet1 changes by the user input in sheet2. the macro search and cross check the values and changes the color of the cell in sheet1.

What is a good way of solving this?

To make it a bit more complicated the user can choose from 4 separate steel bars with different loading data.

to set it up a test I have done all in on test sheet, but I cant get the macro to run for some reason.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCells As Range

Set KeyCells = Range("E4")

If Not Application.Intersect(KeyCells, Range(Target.Address)) _
       Is Nothing Then

    If Range("E4").Value > Range("E14").Value Then
        Range("E4").Interior.ColorIndex = 37
    End If

End If

End Sub

I think the macro will run as long as you got cell's value change at 1st worksheet (Summary) — keong kenshih
You should try Conditional formatting. Faster, cheaper on the resources, and easier to modify later on for other users. — vacip

keong kenshih keong kenshih · Accepted Answer · 2015-07-03T08:28:21

You are comparing the cells value in same worksheet. You need mention which worksheet's cell to compare. In addition, You need set the code for worksheet(summary) activated and worksheet(summary) cell value changed.

worksheet activated:

Private Sub Worksheet_Activate()
Dim MyWorkbook As Workbook
Dim MySummary As Worksheet
Dim MyData As Worksheet

Set MyWorkbook = ActiveWorkbook
Set MySummary = MyWorkbook.Sheets("Summary")
Set MyData = MyWorkbook.Sheets("Data")

    If MySummary.Range("E4").Value > MyData.Range("E14").Value Then
        MySummary.Range("E4").Interior.ColorIndex = 37
        Else
        MySummary.Range("E4").Interior.ColorIndex = 0
    End If
End Sub

worksheet value change:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim MyWorkbook As Workbook
Dim MySummary As Worksheet
Dim MyData As Worksheet

Set MyWorkbook = ActiveWorkbook
Set MySummary = MyWorkbook.Sheets("Summary")
Set MyData = MyWorkbook.Sheets("Data")

    If MySummary.Range("E4").Value > MyData.Range("E14").Value Then
        MySummary.Range("E4").Interior.ColorIndex = 37
        Else
        MySummary.Range("E4").Interior.ColorIndex = 0
    End If

End Sub

Excel VBA - Change cell colour based on event from other sheets

1 Answers