Posts by KiwiSteve

  • help with Msgbox

    Hi hesham


    Try this:
    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim aCell As Range
    On Error Resume Next
    For Each aCell In Sheet3.Range("E32:E1800")
    If aCell.Interior.ColorIndex <> -4142 And aCell.Interior.ColorIndex <> 3 Then
    aCell.Interior.ColorIndex = -4142
    End If
    If aCell.Value <= 2 And aCell.Value <> "" And aCell.Interior.ColorIndex <> 3 Then
    aCell.Interior.ColorIndex = 3
    MsgBox """?C????I ??I ???C? C??I??? ???E ?E? ", vbOKOnly, " Warnning"
    MsgBox aCell.Address
    End If
    Next aCell

    End Sub


    The first loop changes all cell colours which are not red (ColorIndex =3) to 'No Fill'.
    The second loop checks if each aCell is <=2 AND IS NOT BLANK and is not already coloured red. I think that was what your code didn't check.
    You can alter these conditions to suit your needs.


    Note: You'll see more than one condition is checked by the If. . . And . . . steps


    Note: you could also do this using:


    Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("E32:E1800")) Is Nothing Then
    If Target.Interior.ColorIndex <> -4142 And _
    Target.Value <= 2 And Target.Value <> "" Then
    Target.Interior.ColorIndex = 3
    MsgBox """?C????I ??I ???C? C??I??? ???E ?E? ", vbOKOnly, " Warnning"
    MsgBox Target.Address
    End If
    End If


    End Sub


    This has no code to reset all the cells ColorIndex = -4142. Give it a try.


    Cheers


    KiwiSteve

  • Extract & Sort Data

    Hi Dave


    Tried your code, with some mods, and it worked fine. I copied the data to a hidden column on the same sheet to save changing sheets, but otherwise the same.


    Many thanks


    Stephen B

  • Help with Sales forecast

    Hi there


    Attached is a file that predicts for you.
    The data oscillates and so a straight linear pridiction is not useful. So I separated each month out for the nine years, and then used a linear and a quadratic LINEST model to predict for each month. You could use others.


    The a & b for the linear model are the coefficients of y=ax+b
    The a, b & c for the quadratic model are the coefficients for y=ax^2+bx+c
    The R^2 is the co-relation, ie how well each equation fits the data. 0.8-0.9 is considered ok, so most of the predictions are ok-ish!


    The examples on the right show the LINEST equations that are used. They are array formulae.


    The chart has a trendline displayed, with the equation and R^2 value. If you select the chart, you'll see the high-lighted range of data. As you move this to different months, you will (hopefully!) see the values as dispalyed in the table.


    Finally, enter a value into the blue cells for the year period ahead, and you will see a prediction for each month in the green cells.


    Have fun


    KiwiSteve

  • Extract & Sort Data

    Hi all


    I'm familiar with VBA, but have never used arrays. From what I've
    picked up, I would need an array for this task:


    I have up to 1500 data values entered in the range $A$6:$E$305.
    They are recordings and are entered in the order they are taken.


    How do I read all of these values into an array, sort them into
    order, then put them back into the same range so the first 300
    values are in column A, the next 300 are in column B, and so on?


    Also, would it matter if there was, say, only 1178 entries?


    Thank you for any help you can provide.


    KiwiSteve

  • Excel - Combo box

    Hi Sandy


    The default 'value' for a combobox is text, not number, so if the linked cell is A1, then you need to make your formula something like this:
    =if(VALUE(A1)>5, etc...).


    Excel uses VALUE to interpret A1 as a number, if possible, even though it is text. It won't work on "one", however.


    Alternatively, if the combobox is on the worksheet you could use a dropdown list from the Data>Validation>List menu. If the valid list contains numbers, then A1 will also be a number.


    Cheers


    KiwiSteve