It’s safe to assume Style.Fill.BackgroundColor and Style.Font.Color are both of type ExcelColor, so just use the same SetColor() method you used to set the background color. rowRngprogramParamsRange.Style.Font.Color.SetColor(Color.Red);
I have never been in favor of Sendkeys. They are reliable in some case but not always. I have a soft corner for API’s though. What you want can be achieved, however you have to ensure that workbook for which you want to un-protect the VBA has to be opened in a separate Excel Instance. … Read more
This works, even when there are middle names: =MID(A2,FIND(CHAR(1),SUBSTITUTE(A2,” “,CHAR(1),LEN(A2)-LEN(SUBSTITUTE(A2,” “,””))))+1,LEN(A2)) If you want everything BUT the last name, check out this answer. If there are trailing spaces in your names, then you may want to remove them by replacing all instances of A2 by TRIM(A2) in the above formula. Note that it is only … Read more
Thank you for your answers! They definitely led me on the right track. But none of them completely got me everything I wanted, so here’s what I actually ended up doing. The key piece of information I was missing was that I needed to put the data together in one big block, but I could … Read more
This is not necessarily a VBA task – This specific task is easiest sollowed with Auto filter. 1.Insert Auto filter (In Excel 2010 click on home-> (Editing) Sort & Filter -> Filter) 2. Filter on the ‘Websites’ column 3. Mark the ‘none’ and delete them 4. Clear filter
Excel’s interface for SQL Server queries will not let you have a custom parameters. A way around this is to create a generic Microsoft Query, then add parameters, then paste your parametorized query in the connection’s properties. Here are the detailed steps for Excel 2010: Open Excel Goto Data tab From the From Other Sources button choose … Read more
In vba the function is MOD. e.g 5 MOD 2 Here is a useful link.
You could try this =IF(ISNA(VLOOKUP(<single column I value>,<entire column E range>,1,FALSE)),FALSE, TRUE) -or- =IF(ISNA(VLOOKUP(<single column I value>,<entire column E range>,1,FALSE)),”FALSE”, “File found in row ” & MATCH(<single column I value>,<entire column E range>,0)) you could replace <single column I value> and <entire column E range> with named ranged. That’d probably be the easiest. Just drag … Read more
The problem seems to relate to the way Windows handles non-native resolutions on monitors and can be avoided in several ways The problem can be a complete nightmare when it happens, but it only happens intermittently. We have been testing recently an excel worksheet used by a few dozen people and have developed a good … Read more
This is what you need: =NOT(ISERROR(MATCH(<cell in col A>,<column B>, 0))) ## pseudo code For the first cell of A, this would be: =NOT(ISERROR(MATCH(A2,$B$2:$B$5, 0))) Enter formula (and drag down) as follows: You will get: