change cell colour when opening workbook

I would like to change the colour of two cells (C3:C4) to red every time my workbook is opened. The code I have tried is in my Workbook_Open event but I’m getting Application-defined or object-defined error. Here is the code: Private Sub Workbook_Open() Worksheets(“Balance Sheet”).Range(“C3:C4”).Interior.Color = vbRed End Sub Is my syntax wrong or is […]

Excel formula to calcuate different involving two tables

I need a help with Excel formula. I tried a lot since yesterday and have looked at almost all the excel forums and I didn’t find an answer to this. I have 2 tables in an excel (Table-1 and Table-2) Table-1 Name Amount Balance R1 50 0 (This should be calculated using a formula) R2 […]

How to Get reference data from Clients other than excel

Currently collecting all the reference data from Clients through Excel Sheet. My company administrator once receives the Excel sheet from clients will write queries in Excel, validate the data manually and insert in to Application database. Is there any way other than Excel, to validate the data immediately when the customer enters the data and […]

Save Xlsm file as xlsx

I want to save the current xlsm file as xlsx, so i wrote a code as below. This code really done it job and i able to see the file saved as Myfile.xlsx as what i’ve defined in the code, however there is some small issues where the vba always save the file to another […]

VBA to Compare Row Value to Column Value and highlighting Cooresponding Cell

I have to pivot table that has date range down the rows and a different date value in the column headings. I would like to be able to highlight the values where the dates are equal one color, and then highlight the cells that are off by one day a different color and so forth. […]

How to Get Earliest Date Based on Criteria for Large Table?

I have a spreadsheet with ~550k rows. It has Data in first 3 columns, then a calculation field in the 4th like this: The 4th column is a calculation that finds the earliest date (in column A) based on the users name (column C). I am using this formula: =MIN(IF(C:C=C2,A:A)) Followed by pressing Ctrl+Shift+Enter rather […]

Application function match two variables getting run time error

Set rng1 = Sheets(“Reference”).Range(“a2:a2000”) Set rng2 = Sheets(“Reference”).Range(“b2:b2000”) var1 = ActiveSheet.Range(“c2”).Value var2 = c.Offset(0, -1).Value c.Value = Application.Match(criteria1 & criteria2, rng1 & rng2, 0) I am trying to match two variables from one sheet to another to see if the value exists with both criteria in the reference worksheet, but I keep getting a type […]

Excel Formula to return earliest date within “conditional range.”

Not sure if the question is phrase properly, but here’s my problem. I have two columns. One with dates (colum A), and one with values (column B). In the column B, some cells are blank. I want to write a formula that returns the maximum value from the column A, but only for cells in […]

get distinct list (index) if adjacent value equals a specific text in excel

I would like to get a distinct list of column J if cell in H of same row equals “No Company”. I tried =IFERROR(INDEX($J$2:$J$10, MATCH(0, COUNTIF($K$1:K2, $J$2:$J$10&””) + IF(ISTEXT($H$2:$H$10)=”No company”,1,0), 0)), “”)) also tried =IFERROR(INDEX(J$2:J$400, SMALL(IF((H$2:H$400=No company)>1)), ROW(j$2:j$400)-ROW(j$2)+1), ROWS(j$2:j2)), “”) changing some of the fields but no luck Desired result + Try

mso-number-format for :mm when hh > 24

When I set a custom formatting of [h]:mm the css file Excel generates shows: [h]:mm. However when I define the mso-number-format as [h]:mm I don’t get the expected [h]:mm custom format. Worse: it generates an [h]:mm:ss custom format for any hour more than 24. Who needs those seconds! Any hint for elapsed time formatting? Thanks!

MS Excel Spreadsheet is the best Office Software, Excel VBA and Excel Formulas make Spreadsheet work faster.