Posts by Neale

    Re: Sorting Data

    Excel 2007 + 2010 will allow you to sort by colour. You could then put a sequence number in the coloured cells to represent the sort order you require and then resort using that number.

    Ctrl + Enter allows you to enter the same number in the selected region.

    If you are in Excel 2003

    The colour numbers (255,10498160 and 65535) used in the macro will need to be amended to match the colours numbers you have used

    Warning: This macro assumes No Entries in column B where the colour is. If there are entries it will need to be amended to put the number in another column.

    This macro will put a number in the cell based on the colour. Select the range in Column B and run the macro.

    Sub Add_Number_To_Colour()

    Dim c

    For Each c In Selection

    Select Case c.Interior.Color

    Case 255 'red

    c.Value = 2

    Case 10498160 'purple

    c.Value = 1

    Case 65535 'yellow

    c.Value = 3

    Case Else 'other colours

    c.Value = 4

    End Select

    Next c

    End Sub

    Re: Advanced use of Excel's comments

    Looks like a macro solution to me when using comments.

    Not sure if using photos in comments is the best way to handle this.

    You can put a photo in a cell and use range names and PastePictureLink feature to "lookup" a photo.

    Link below demonstrates a technique using range images and range names for dashboards - you may be able to apply yo your situation.…l-intheblack-2011-10.html



    Re: Extract a set of data to a new worksheet based on conditions

    Check out Advanced Filters - they allow you to used formula in the criteria and you could use MIN and MAX.

    Assume ABC for your columns above

    If the lists is sorted by date and time then this formula will extract the first price for the day in cell E3.


    Also array formulas can allow you to extract more complex criteria

    Assume ABC for your columns above

    Max price for the day
    Press Ctrl + Shift + Enter after typing


    E2 contains the date you are looking for

    Change MAX to min for the lowest price of the day

    Hope that helps



    Re: VBA SQL not updating in all situations

    I figured it out.

    The code uses the "current" folder with the data base name. When you open Excel thru Win Explorer the current folder is My Documents - not the folder of the file - which was assumed to be in the same folder as the database.

    I amended the code and defined the folder using ThisWorkbook.Path plus the DB name - now its all good.

    Re: Problem simplifying a recall look-up over several columns


    I think an array is the only answer. This worked - you will need to fix your references as per below for copying

    in yellow cell H86


    You have to enter this using Ctrl + Shift + Enter to enter as an array - this puts { } around



    Excel 2003 and Access 2003 Windows Xp and WIn 7

    I've recently started using SQL via VBA to update Access data tables.

    My problem is that if the Excel file (with the VBA SQL code) is opened through Windows Explorer (Excel closed) the code won't update Access at all. All the other macros work ok just the SQL code doesn't update Access.

    If the file is opened thru Excel it all works fine - no problems at all. If you have Excel open and then open the file thru Win Explorer it also works ok.

    Is there a setting that Excel changes when opening thru Win Explorer?

    Any help appreciated.



    Hi all

    The macro below runs no worries on XL2000 and XLXP but slows down to a wet week when it runs on XL2003.

    The macro hides rows in a specific range based on the value of a cell in the range.

    Any ideas why?

    I may have revert to use autofilter to do the hide if I can't find a solution.


    I'd say you need a macro to do that.

    Excel won't do that dynamically. You'd need a macro run whenever an entry is made in C1 - it would check the existing list and add it if it couldn't find it.

    You could write formula or use a conditional fomat to tell you whether or not the entry is in the list then have a macro copy it to the list when you hit a button


    I've heard that Access has its own problems (speed) when being used in a multi user network environment. I don't know if newer versions have solved the problems.

    I think you have to have the full Access program, there is no "viewer" as for powerpoint. Which usually means you have to buy Office Pro.

    There are some add-ins or mods to Excel that make it more network friendly tho I haven't used any personally. I've heard that they can make Excel like a multi dimensional DB.


    How about

    Sub TryMe()
    Dim rCell As Range
    Dim rRange As Range

    Set rRange = Range("c1", Range("c65536").End(xlUp))

    For Each rCell In rRange
    If rCell = rCell.Offset(1, -2) Then rCell.Offset(1, -1) = 0
    Next rCell
    End Sub


    same code with small change - untested

    Sub TryMe()
    Dim rCell As Range
    Dim rRange As Range

    Set rRange = Range("A1", Range("A65536").End(xlUp))

    For Each rCell In rRange
    If rCell = rCell(0, 3) Then rCell(0, 2) = 0
    Next rCell
    End Sub

    Maybe something like this (untested)

    You might need to identify the textbox more exactly as you are moving between sheets.

    Private Sub CommandButton1_Click()
    Dim c

    for each c in worksheets("form").range ("A1:A112")

    If c.Value = TextBox1.Text Then
    MsgBox "The value you entered is " & Range("A1").Value, vbInformation

    End If

    next c

    End Sub