Posts by Mark

    Thank you for your reply,

    The first thing I originally tried was to record a macro, but it does not write any vba for :-

    selecting menu item=cf,

    entering the Formula

    selecting the formats


    I just tried again, recording the macro with the VBA window open (ALT=F11)

    and could see it writing code for normal actions like ...Range("D45").Select

    but once I started selecting menu options, nothing got written to the new macro.

    that is why I tried finding code examples on the internet, but I couldn't work out the syntax.

    The code I found/worked out can work:-


    Set condition1 = rng.FormatConditions.Add(xlExpression, Formula1:="=" & Selection.Cells(1).Address(False, False) & "=MIN(" & Selection.Address & ")")

    does work (copied direct from i/n)

    but my attempts (among many others) to write the formula I want, such as:

    Set condition1 = rng.FormatConditions.Add(xlExpression, Formula1:="=SEARCH("ISA",$A10)


    Set condition1 = rng.FormatConditions.Add(xlExpression, Formula1:="=SEARCH(""ISA"",$A10)"

    get rejected

    I have a pivot table whose source data is itself of variable size, and needs to be refreshed frequently.

    This causes the pivot table to loose my 6 previous rules of conditional formatting, because the CF range sizes have changed.

    I was thinking that if I could write the CF rules by macro then I could auto renew them as needed.

    learning how to write CF rules by macro would help in many other workbooks & worksheets when my CF rules-ranges get jumbled up as I copy & paste.

    Many of my CF rules use formula, which work, but which I have been unable to convert to VBA.

    thanks for your time

    I am trying to create multiple Conditional Format rules using VBA, but have been unable to work out the right syntax.

    Using Excel itself I get the correct result as follows:-

    > Home > Conditional Formating > Manage Rules > Edit Rule

    >>> Rule type ..... formula

    >>>Rule description ..... =SEARCH("ISA",$A10)

    >>> formats are set

    >>>Applies to ..... =$A$10:$Q$100

    What is the right way to convert the Rule-description-foumula into VBA?


    This is the best I have been able to come up with:-

    Re: vba__insert to cells =average() with variable range

    Hi skywriter

    Thank you for your code, I have tried it out and seen what it does. It is not quite what I needed;
    however I continued to search and found another answer:-

    ActiveCell.Offset(0, 1).FormulaR1C1 = "=AVERAGE(R[-3]C[0]:R[-09]C[0])"

    this inserts a formula into the cell one column to the right of the ActiveCell
    The formula then skips the two rows above (and in the same column),
    and then selects the range of 7 rows upwards.

    to get VBA to write the full Report Summary:-

    Re: vba__insert to cells =average() with variable range

    Quote from skywriter;799868

    I would suggest you upload an actual workbook.

    I have uploaded/attached a cut down version of the table

    The whole table is actually created by macro from supporting sheets (not included)

    my macro "Experimental_Report_Summary" prepares what I am aiming for; but the formulae at the bottom of the report are absolute references and do not alter as I add new rows. I want to write vba which will insert this summary with formulae that are relative to the bottom row of data.

    my macro "Experimental_Report_Summary_2" shows some of my failed attempts.

    any help much appreciated

    I have a Big table of data which adds a new row for each record (per Day).
    At the bottom for some columns I want to add the formula for 7-day average and 28-day average to some columns.

    the following code does what I want but does not adapt to the addition of new Rows of data

    I have researched and experimented with various alternatives to define the ranges to be averaged

    but am unable to work out the proper syntax to define the ranges to be averaged so that they are not absolute addresses.


    Re: Jump to Formula-source-cell

    Quote from soulfire21;662740

    You can do this:
    =HYPERLINK("[Book1]Sheet2!M1","Go To M1")

    The Book1 references the Workbook you'd like to go on, Sheet2 the sheet, and M1 the cell. The text "Go To M1" appears in the cell that contains the HYPERLINK formula.

    You must include the entire reference within quotations, for example: "[Workbook1]Sheet1!A1" will jump to Workbook1, Sheet1, Cell A1 when clicked.

    Thank you for your reply,

    If I use this method I think I need to separately create each hyperlink, which is what I do at the moment.

    Is there a way to generalise the formula?
    I would like the hyperlink to goto a location based on the formula in the cell to the Left/Right/Up/Down of the hyperlink-cell


    Re: Jump to Formula-source-cell

    Quote from cytop;662611

    Try, in the DoubleClick event handler:

    .Goto Sheets(Mid(v(0), 3, Len(v(0)) - 3)).Range(v(1))

    Thank you,

    This works with my example sheet, and shows that what I am looking for can be done :)

    However this particular code appears to only work with the particular worksheet names in my example workbook. I am hoping that there is a way to generalise the code, so that I can copy it from one worksheet to another and into other workbooks which have different sheet names.

    It would be so clever to be able to double-click a formula and then jump to the data-source of the formula. Any further ideas welcomed.

    meanwhile as a different way to achieve something similar:
    I have looked at the =hyperlink function
    and it seems to me that I should be able to write a hyperlink which referenced a formula in a different cell, and would jump to the data source of the different cell ....

    if cell A1 is ... ='. (DifferentSheet)'!M1
    and cell B2 is something like ... =hyperlink(___A1___,"linkOfUp")

    I would like to be able to
    * click the hyperlink in B2, and jump to cell M1 on DifferentSheet
    * copy & Paste the formula in cell B2 and for the pasted cell to always create a hyperlink to the data-source referenced in the cell above

    what is the real formula that I need in cell B2?

    Thank you for your attention

    Re: Jump to Formula-source-cell

    Hello Stephen

    THANK you for your reply. I like very much the way it begins when I double click on a cell.

    Unfortunately I have not been able to make it work properly as it crashes at line "goto sheets" with an "run time error 9"

    I attach a cut-down version of my much bigger spreadsheet, as an example of what I am trying to achieve .... On the summary sheet I would like to be able to select a cell, then jump to the data source. Is there something in the way I have designed my data and worksheet names which is causing your suggested code to fail?

    Thanks for your attention

    Re: Jump to Formula-source-cell

    Quote from StephenR;662105

    Have you considered Trace Precedents in the formula menu?

    Thank you for your reply....
    Yes I had considered Trace Prededents, but as the formulas refer to cells within other worksheets, all I get is a line-&-little-Grid symbol.

    I can't seem to click onto the grid to jump to the source data ... am I supposed to be able to?

    Also; I have scores of inter-worksheet-formulae that I want to jump between. After a few jumps Tracing precedents would clutter the display with blue lines

    any other suggestions?



    I have spent today searching and have seen LOTS of messages about hyperlinks, but I have not been able to understand enough to adapt/create my own answers

    I have a workbook with many sheets, an each sheet there are several/many formulae which dispaly values from other sheets within the same workbook.
    eg =WeightHistory!B189
    In a cell next to the formula I have manually added hyperlinks to the formula's-data-source.

    The workbook is now cluttered with my hyperlinks; which also break down whenever I add a new row or column.
    I have been reading about Dynamic-hyperlinking, but have not been able to get it to work;
    Perhaps hyperlinking is a wrong approach?

    I am looking for a way to:-
    Click on the cell containing the formula, and then auto-jump to the source-data of the cell/formula?

    or to:
    select a cell; and run a macro shortcut which would check the formula within the active cell and then jump to the source-data.

    or to:
    create a hyperlink formula which would read the formula of the cell next to it, and provide a hyperlink to that formula's-data-source

    can anyone help?

    (Is is possible to add a macro-shortcut to the mouse's right-button-click menu?)


    Re: highlight current cell with or without conditional formating

    Thanks for your previous message:

    I know that it was posted 4 1/2 years ago, but it has just worked for me :)

    I have a spreadsheet with many conditional formats and after several hours checking out other "solutions" only your idea is working for me ... thanks.

    Ignorant question ... Do I have to post the code into each VBAProject / microsoft excel object > sheet#(??)
    As I would like the feature to work on ALL sheets is there a place to store it once only, and for it to apply to all sheets?


    Re: Test if cell contains integer

    Hello cytop,

    Thanks again for such a fast reply.
    Using your previous reply I have now achieved the conditional formatting which I was aiming for:-
    Starting with cell A295 if I add conditional formating rule :-
    Rule: =INT($A295)<>$A295
    Applies to: =$A$295:$B$312

    Then I can copy the format to range A295:b312
    meaning that the formatting for each row is dependent on the contents of column A for that row (ie is it an integer?)

    **Thanks for my solution.**

    As for Check_cell, I have had a look at the link and the information is well beyond my knowledge level (sad smile) However I plan to experiment with the methods & properties later on, so thanks for that referal also.


    Re: Test if cell contains integer

    Thanks Rob & Cytop, for the fast answers

    I will give these a try.

    However as a more general issue ... would it be possible to create other tests such as:-
    Function IsInt(Check_Cell As Range)
    IsFormula = Check_Cell.HasInteger
    End Function

    What I don't know about is, what options can I use with Check_Cell.Has??? to check for other stuff.

    does this make sense?


    I have recently found the very helpful =isFunction UDF
    Function IsFormula(Check_Cell As Range)
    IsFormula = Check_Cell.HasFormula
    End Function
    which I dont understand but can use nicely to do very useful conditional formatting.

    I have done 3 hours of looking (F1 help, search ozgrid / search google etc) without getting very far, enough to think that it ought to be possible to create other types of cellchecking.

    Is it possible to create other 'cellchecking' functions? what are they? can I find a list somewhere?

    specifically can I create a function which checks if a cell's contents are an integer (rather than a decimal)?


    Re: Enhanced Workbook Index

    Hello Norie

    Thanks again for your code improvements. I have studied it and fiddled around with it to make the output look formated in a way I like. It works well for me.

    For the sake of anyone else who may be interested I list the final version below: (I expect the code changes are cumbersome and basic, but they get done what I was looking for)


    Re: Enhanced Workbook Index

    Sorry for the delay in checking back to this forum over Christmas.

    Thanks for your reply, I will try it out when I next get home (probably next week) and let you know.



    Is anyone kind enough (clever enough?) to help me change the following code which I downloaded from this forum a few weeks ago.

    I have tried experimenting, but as I have no training as a programmer I am getting it wrong.

    This super, short code creates an index page of all the worksheets within a workbook

    [1] One each existing workbook
    I would like the code to format the [Back to Index] text that it puts in cell A1 on each sheet. For example to
    Align vertical = centre
    Align horizontal = centre
    Font Sixe = 8
    Backgroung colour = beige
    text colour = blue

    [2] The current index page lists all worksheets in column A

    I would like the code to format the cells. For Example
    font size = 18
    column width = 25

    It would be just spiffing if I could get the cell to toggle/change background colour then the mouse cursor went over it

    And the one I have really struggled with ...
    I would like the code to add new data in columns B & C.

    * each time the code puts a worksheet name/link into column A
    * I would like the code to move to column B and input a formula which picks up the contents of cell B2 from the worksheet which it has just listed in column A
    * then I would like the code to move to column C and input a formula which picks up the contents of cell B3 from the worksheet which it has just listed in column A

    The idea is to build a index page which picks up title and description information from eah of the worksheest in the workbook.

    I think this would make a really neat index page, and I am hoping that someone clever thinks so too :)

    Thanks for your attention

    Re: define range anchor points within a formula by cell values


    With a bit of tweeking I put in a middle step so that all I need to do is enter the start and end row values into two cells, and the whole graph is updated. Then by liberal use of range names the formulae become virtually understandable:-)

    to wit:
    step1 - name two cells StartCurrent & EndCurrent

    *** calculate bigCompare range and display by string formula
    ...="$B"&+StartCurrrent&+":$B"&+EndCurrent to give ... $B467:$B832

    *** calculate SumifRanges for each column and display by string formula
    ... ="$D"&+StartCurrrent&+":$D"&+EndCurrent to give ... $D467:$D832
    ... ="$E"&+StartCurrrent&+":$E"&+EndCurrent to give ... $E467:$E832

    (I'm sure I can now get rid of absolutes too)

    I now have the 'text' decsription I need for the INDIRECT function which you showed me

    and I end up with nice graph-data formulae such as

    This technique would have bee soooo useful in the past, thanks for the tuition.


    Post back if you need more help.

    A rash offer!

    Actually yes,there is one more refinement I would like.
    I would like to be able to type in the relevant start & end dates and then for the StartCurrent & EndCurrent cells cells to look up the row number in the source data table.
    any ideas?

    I attach my revised version in case you are intrigued to see what I've done with your help.