Excel column range highlighting, and insertion of calculation data

Question

I have the following problem:

I need to highlight period of production order start and finish week, through all columns - as you see in header, there are a lot of production orders.

Is it possible with vba?

Second problem:

Each production line has time capacity, so production minutes should be divided by chosen production line weekly capacity by the end of highlighted period.

This is also need to be done on button click e.g. with vba, or formulas, but I have no idea where to place those formulas, because whole table and header supposed to be dynamic - production plan updates weekly.

UPDATE:

Here's code, that inserts formula in order manufacturing period from start to finish week, i need to loop it through all columns and insert this formula according to following orders start and finish week. And I have no idea how to do that, so help is very appreciated :)

Sub test()

    Dim Rng, Rng2, Rng3 As Range
    Dim v1, v2 As String

    x = Range("E8")
    w = Range("E11")
    'Search Column or range
    With Sheets("forecast").Range("D:D")

            Set Rng = .Find(What:=x, _
                            After:=.Cells(.Cells.Count), _
                            LookIn:=xlValues, _
                            LookAt:=xlWhole, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, _
                            MatchCase:=False)
            Set Rng2 = .Find(What:=w, _
                            After:=.Cells(.Cells.Count), _
                            LookIn:=xlValues, _
                            LookAt:=xlWhole, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, _
                            MatchCase:=False)

                    'MsgBox Rng.Offset(0, 1).Address
                    'MsgBox Rng2.Offset(0, 1).Address
                    v1 = Rng.Offset(0, 1).Address
                   v2 = Rng2.Offset(0, 1).Address

            Range(v1 & ":" & v2).Select
            Selection.formula = "=E$12/(E$10-E$7)"
            Selection.NumberFormat = "0"

    End With
End Sub

Example of production plan:

http://postimg.org/image/3vkx0fv5d/ http://s8.postimg.org/cdud4s1o4/stack3.jpg

score 0 · Accepted Answer · edited May 23 '17 at 12:33

0

As for highlighting you need to put in a "conditional format". (It's on the home ribbon.) Select the cells from column D11 till the end of the column range needing the formula. Click conditional format and enter the formula:

=AND($B11>D$6;$B11<D$7)

Make sure the dollar marks are in the right spots. And set a format to apply, for example fill to be red. Apply the formula, you just made a conditional format for column D. Now select cell D11 and click "format painter" button and select the whole range to which you want to apply the formula. Done.

update,Adding year:

I only see one year in the sheet. i.e. at the top. So if start/finish week could be of some other year as well that should be added as extra lines start year and finish year. Then create extra lines and columns for the excel date_formats created with help of the formula: creating excel_date_format

Then you can use the same CF as before now using the newly added column and lines as in: like it would look if you don't hide the newly added orange coloured entries

edited May 23 '17 at 12:33

Community

1
1

answered Nov 19 '14 at 16:47

Robbert Koppenol

103
6

how to make this formula recognize year? – antimaniak Nov 20 '14 at 09:38
Hey! Is it possible to define every column range in VBA using two cells with date formats, and insert formula in every cell of defined range ? – antimaniak Nov 24 '14 at 11:04
I mean fill red areas with formula, using vba ? – antimaniak Nov 24 '14 at 11:22
Yes but I am not writing up VBA. Why not just use the conditional format. You can just hide the orange colored rows and column if need be (they are results from formulas so don't need to be in view) – Robbert Koppenol Nov 24 '14 at 12:29

Excel column range highlighting, and insertion of calculation data

1 Answers1