Excel Vba Find Cell Address Based On Value

Usually in excel when we are working we need to highlight some of the rows based on the value in the other cell. The policy on this board is you do not post questions in threads started by other members. Or how to delete all rows that contain certain value with VBA code in excel, such as, removing all rows if cell contains 0 or any other value. Worksheets is a collection of Worksheet objects. Address which, in this case, returns something along the lines of R[100]C. Click to select series. Referring to Ranges in VBA. Example 1: Select a single cell and refer a whole range of cells. Examples of these events include changing a cell in the worksheet, changing the selection, double-clicking on a cell, right-clicking on a cell, etc. Re: Return cell values based on cell color Hi Surya51, The problem then is in the directory code - I solved that for my machine by inserting the slash - take a look at how your files are named and tweak the code to match the way they are - (the code I gave you runs on my system). Select a cell. The CELL function returns information about the formatting, location, or contents of a cell. Type the heading Original in cell A4 and the heading Replace in cell B4. Value = i * j Next j Next i End Sub Related examples in the same category. Click a cell in the range. How To Trigger Your VBA Macros To Run Based On A Specific Cell Value Change August 15, 2015 / Chris Newman Today we are going to discuss how you can automatically make your VBA code execute based on a specific cell value being changed. If the cells have moved up, Microsoft Excel creates a cell in the last position of that column. address In Excel 2013 you will get Copy Entire Row to different sheet based on cell value - Page 2. You can see the address of active cell displays on the cell A1, and the address will change automatically based on the changing of the active cell. Here, cell B11 uses data validation to return the selected name from. Sorts the contents of a range or array based on the values in a corresponding range or array. VBA code to go to cell based upon cell value I need VBA code to look in F10::Z10 and go to cell that has "ABC" in it. He had a workbook with many sheets including an "overview sheet" with key data from the other sheets. Re: VBA Lookup address of cell in Range based on value selected in combo box Excellent thanks soo much. CELL retrieves the workbook name and sheet, and the MID and FIND functions are used to extract just the sheet name. Find next empty cell with Excel VBA. You can use your spreadsheet and code from the Change Case section for this. So far, we've looked at how to find out the address of a cell in our worksheet. Two ways to change background color in Excel based on cell value by Svetlana Cheusheva | updated on September 18, 2019 326 Comments In this article, you will find two quick ways to change the background color of cells based on value in Excel 2016, 2013 and 2010. Here is a function that does just that. Select that row. Selecting multiple ranges in Excel VBA helps your code to work faster. Add the following lines of code: Dim Number_1 As Integer Dim Number_2 As Integer. In a large Excel spreadsheet containing lots of worksheets, it may be useful to build an index list - possibly to read the values from a particular cell reference on each sheet. Now let's discuss how to use Excel to reference a cell in another worksheet based on cell value. REPLACE in VBA (#vba) In VBA prior to Excel 2K use application. The Lookup Wizard uses INDEX and MATCH in the formulas that it creates. Both worksheets are in the same workbook. Re: Find Value And Return Row Number Welcome to Ozgrid, LynChen. As demonstrated in the above example, you can use the Excel INDIRECT function to put the address of one cell into another as a usual text string, and get the value of the 1 st cell by referencing the 2 nd. Value = Inputbox("Enter the number?. The Lookup Wizard uses INDEX and MATCH in the formulas that it creates. 6 KB) To find the last used row in a column, this technique starts at the last cell in the column and goes up (xlUp) until it finds the first non-blank cell. We’ll briefly explore one simple example of using VBA in Excel to extract values from one worksheet , but this is just the tip of the iceberg for what Excel and VBA can accomplish together. VBA: Get cell value based on row and column numbers: Function GetValue(row As Integer, col As Integer) GetValue = ActiveSheet. Intuitively, you would be doing in VBA what you would do by hand. You can see the address of active cell displays on the cell A1, and the address will change automatically based on the changing of the active cell. Found variable doesn't contain a String, you've declared it As Range, so it contains a Range object reference. In the VBA programming language, the minus sign (-) is used to subtract one value from another. Create Chart using Named Range in Excel Creating a chart that uses a named range is excel can be accomplished by following these basic steps. Read or Get a Value from a Cell Using VBA This tutorial shows you how to pull values from cells in Excel into a macro. Set cell value with For Loop Sub RangeObjects() Dim i As Integer , j As Integer For i = 1 To 10 For j = 1 To 5 Cells(i, j). I have to find a value celda in an Excel sheet. I wrote the example, when a reader asked for help. How to find or select cells based on certain criteria in Excel? Supposing you have a data range as following screenshot, and now you want to find or select cells based on certain criteria information in Excel, for example, I will find or select the cells which contain the numbers between 80 and 100. This method only selects one cell at a time by clicking on the Find Next button after completing all the required steps. Find the first cell that contains the saved value - either 1, 2 or 3 - within the row selected in step 10. How To Find Cell Address Of The Maximum Value In A Column In Microsoft Excel In this article, you will learn how to find address of the cell containing maximum value in a column. Add values to table. I have to find a value celda in an Excel sheet. VLOOKUP() - Get value based on another value Published by jppinto on 21. The following example displays four different representations of the same cell address on Sheet1. This is the way by which we can get the value of the last non-blank cell in a column in Microsoft Excel. Value = 5 Set c =. Find strFirstAddress = rngFindValue. Find is a very powerful option in Excel and is very useful. I don't think it sould cause any problems. Whenever there is any change in the source data, your OFFSET formulas are recalculated, keeping Excel busy for a little longer. The macro will then check every cell within your specified range and create a new range whose cells are within the minimum and maximum values specified. I know how to place the output of the function in a single cell. Excel VBA Find: Step by Step Guide and Examples to Find Values, Strings, Characters with Macros By J. The policy on this board is you do not post questions in threads started by other members. That is, when the cell’s contents change, the sheet’s name changes. Fill Down based on Criteria Unfortunately There's no IF option on the Fill tool but it's easy enough with a combination of Go To Special and an IF formula : Select the cells in column E from the first row of your data to the bottom of your data e. Substitute probably can be greatly reduced based on the technique used above. Find(2, lookin:=xlValues) If Not c Is Nothing Then firstAddress = c. A collection is a group of objects of the same class. Hey, I try to write a vba function to find the cell address for a particular cell that match the search criteria. The comments in the example are the addresses that will be displayed in the message boxes. then it will ask you that enter the criteria the condition based on which you want to trigger the deletion, then it will ask you the column in which the value need to be looked in. You can select multiple ranges in single line code and perform the action you want to perform. Fine, so what are some reliable methods for finding the last used cell in an Excel worksheet or a range? It depends on what you mean by the last cell? The following code sample show my ideas for how to tackle the following scenarios: Find the last cell that contains data in a row or column; Find the last cell that contains data in a range. The search is case-sensitive. What i am looking to do is based on a value in a specific cell, i want to search a column of text and if that text is found, go to that cell. You may need to change the value in a cell when working with Excel and Visual Basic for Applications, or VBA. Edit cell based on row and column value vba. This sub also runs when connected with a button, so you might have to put an autorefresh sub in if you want it go automatic but it can also slow down your workbook. VLOOKUP() - Get value based on another value Published by jppinto on 21. We can restrict data entry for a range of cells using the Excel Data Validation. Example of using simple SSH client for uploading and downloading files based on Excel. Sheet_text: The name of a worksheet to use in the reference. For example, you may have a cell that contains a combination of text and numbers, or a cell that contains two numbers separated by a delimiter such as a comma. However, most of them focus on finding rows where the value in just one column is duplicated. Excel IT Pro Discussions https:. The alternative it to draw the cells from under it up. VBA code to hide or unhide rows based on a cell value Browse other questions tagged microsoft-excel vba cells or ask your Excel - find MAX value in multiple. Sub Range_Address3() Set MyRange = Worksheets("021817"). Value (Range. Example: In cell A1 I have 500 In cells B1:B10 I have 100 900 100 500 400 700 200 600 200 300 I want to start in B10 and move up then stop in the first cell that is equal to or greater than what is in. Cell B1's value = 0. VBA code to hide or unhide rows based on a cell value Browse other questions tagged microsoft-excel vba cells or ask your Excel - find MAX value in multiple. To display the reference style of the cell address. If you want to write the data to another sheet, you have to mention the sheet name while writing the data. Find(2, lookin:=xlValues) If Not c Is Nothing Then firstAddress = c. Cells, where expression is an expression that returns a Range object, to obtain a range with the same address consisting of single cells. then it will ask you that enter the criteria the condition based on which you want to trigger the deletion, then it will ask you the column in which the value need to be looked in. This is my first time on the site and hopefully someone can help! I have specific text in column D that I want to look-up replace with text from column B if the value in column D is equal to column C, but within a range of rows sharing a the same cell reference in column A. itself based on values and everything. TRANSPOSE function. As a VBA function, you can use this function in macro code that is entered through the Microsoft Visual Basic Editor. Then you are ready. In the example the formula we're using is: Excel formula: Get first non-blank value in a list | Exceljet. Excel's built in worksheet functions provide almost no support for working with hidden ranges. For example, if you want to verify that a cell contains a numeric value instead of text before you perform a calculation on it, you can use the following formula:. A cell address is returned as a text string, not an actual reference. Close the VBE (Alt + Q or press the x in the top right corner). It sets a cell active and then put the value in the active cell. Get cell value based on row and column numbers with User Defined Function. like when you say, activesheet. For versions of Excel lower than 2007: the number of colors is limited (the closest available color to the RGB values will be used). Basically i'm copying a cell value and would like to find the value on other sheet. 6 KB) To find the last used row in a column, this technique starts at the last cell in the column and goes up (xlUp) until it finds the first non-blank cell. In fact, it might even be faster to use this instead looping through multiple cells or rows in VBA. Cell B1's value = 0. Why? Because there are 65,780 possible five-character combinations and only 65,536 rows in which to place them. Hello, Here is what I am trying to do with this piece of code. Find is a very powerful option in Excel and is very useful. He had a workbook with many sheets including an "overview sheet" with key data from the other sheets. In Excel VBA you can get the value (either Text or numbers) from a cell by referencing the cell and using. The Excel Range is also a Worksheet property that returns a subset of its cells. What I am trying to do is retrieve the values from those using the address. Find value in Range, Sheet or Sheets with VBA. However, there is no method to determine the contents of the cell with the Event Change feature. You can not select a cell with formulas in Excel. VBA and Excel support only a primitive method, the Range. REPLACE in VBA (#vba) In VBA prior to Excel 2K use application. How to write VBA code (Macros) in Excel? To write VBA code in Excel open up the VBA Editor (ALT + F11). As a VBA function, you can use this function in macro code that is entered through the Microsoft Visual Basic Editor. Test if the sum of the cells with values 2 => X and if so return a message "yes you reached your goal" , if not continue checking until a total of 95 cells to the right from. Find is a very powerful option in Excel and is very useful. Excel Experts, Formula for file path You can use the excel function call CELL("filename") to retrieve something that should be the file name, but for some odd reason isn't. How To Trigger Your VBA Macros To Run Based On A Specific Cell Value Change August 15, 2015 / Chris Newman Today we are going to discuss how you can automatically make your VBA code execute based on a specific cell value being changed. We can restrict data entry for a range of cells using the Excel Data Validation. This page describes how to work with visible and hidden cells. 09 / Tags: IF , ISNA , VLOOKUP If we have a table of data, like the one shown bellow, and based on a value of that table want to return the value from another column we can use the VLOOKUP() function. VBA Tips - Import A Text File; how to arrange from max to min the results (from formula). An essential skill any Excel user should know is how to determine the range of values in Excel. Searching and getting value from external workbook based on certain condition and pasting value along with format style using VBA in Microsoft Excel. When you are writing code in VBA, you can use the Text property of a Range object to get the text which is displayed on the screen. vba to place data in cell based on value of another cell a1 is a numeric value changing by 1 each week (ie: if a1=1 this week it will equal 2 next week) cell b1 has a formula. than what that user was asking for so I need a bit more help tuning the VBA. in your case if it is column A then type 1 and if it is column B type 2. This meant I didn't have to go into my macros and change range addresses in my code every time the size of my data changed (which was typically every day). However, there will be situations when you want to highlight an Entire. To get the first non-blank value (text or number) in a in a one-column range you can use an array formula based on the INDEX, MATCH, and ISBLANK functions.  Likewise, Excel tables can be extremely useful in combination with VBA. Hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications window. FindNext(c) Loop While Not c Is Nothing End If End With. I need a VBA code that will change the cell colour of Column B if the text in Column A is different to it. Click to select series. How To Find Cell Address Of The Maximum Value In A Column In Microsoft Excel In this article, you will learn how to find address of the cell containing maximum value in a column. So let's see how can we access a name range in VBA. Which is the "true" last cell depends on the context of the data. First, we use the MAX function to find the maximum value in column A. You would need to use a macro, written in VBA, to actually select the cell. The alternative it to draw the cells from under it up. The macro will then check every cell within your specified range and create a new range whose cells are within the minimum and maximum values specified. In an Excel worksheet, there will certainly be a lot of data and information. Now let's discuss how to use Excel to reference a cell in another worksheet based on cell value. This is not an issue for a single formula in a small spreadsheet. UNIQUE function Returns a list of unique values in a list or range. Value to the ActiveSheet. Add IIf(c = 9, Format(varEach. Alt + F11 to open the VBE. Value = 5 Set c =. How to get cell address from Find function in excel vba. In Excel ‘Go To Special’ is a very handy option when it comes to find the cells with formulas. Here is a brief description of how the code works: This macro loops through all the cells in Range("A1:G1") using a For Loop. Or how to delete all rows that contain certain value with VBA code in excel, such as, removing all rows if cell contains 0 or any other value. In the example the formula we're using is: Excel formula: Get first non-blank value in a list | Exceljet. Range("a1:a500") Set c =. It can be used as a VBA function (VBA) in Excel. Use the AutoFill function by specifying the destination range: 10. For example, Range("C1"). The top cell is give the value, "Description" and the bottom cell is give the value of the search string you provided when you initiated the search surrounded by asterisks. Value in the Change event. Excel VBA custom Function Find the first colored Cell value Posted on February 26, 2015 March 21, 2017 by Wyman W This tutorial explains how to create an Excel VBA custom Function to find the first colored Cell value in a Range using ColorIndex Property. It sets a cell active and then put the value in the active cell. This post will guide you how to use the Find & Replace feature to delete or remove all rows based on certain cell value in Microsoft Excel. ‘Go to Special’ option has a radio button “Formulas” and selecting this radio button enables it to select all the cells containing formulas. Find and replace in range based on cell values Okay, here is what I'm trying to do. If there is no "ABC" then continue with the rest of macro. Select a cell. Microsoft Office > Excel IT Pro Discussions. CELL retrieves the workbook name and sheet, and the MID and FIND functions are used to extract just the sheet name. If active cell is empty then the code will not run. Seat availability (Seat vacant or occupied , which I take care through macro). The only solution is to make VBA code turn on another worksheet before proceeding to cycle through the dependent cells. And here we will introduce 2 useful methods to copy the target cells. Retrieving Values Using References Based on Row and Column Numbers in Microsoft Excel 2010. keys End With With Me. Add value to. This post will guide you how to find the closest value or nearest value in a range of cells or in a column in Excel. Click Insert > Module, and paste the following code in the Module window. Second, we use the MATCH function to find the row number of the maximum value. FindNext(c) Loop While Not c Is Nothing End If End With. then this can be done by creating a. For eg: If B2 has "Yes" than entire row of 2 should be copied in another table. First open the editor, add a module, copy the macro below into it, and link it to a formula button (refer back to the previous page if needed):. Returns the transpose of an array. Set up another Sub and call it Subtract_Numbers. Run or trigger macro if cell value equals specific text with VBA code. To run the macro you will need to specify the range to work on, the minimum value, and the maximum value. For example, you can highlight a particular cell based on a condition. VBA Course: Properties In this exercise, we will write VBA code that modifies the content and appearance of cells and worksheets. To do this, we need a way to write a formula that tells Excel to retrieve the value in a cell, given the address of the cell. The aim is to sum all of the values of cells which are a particular colour. Working with Excel cell values, formulas and formatting: C# code samples Posted on Tuesday, October 15th, 2013 at 9:22 am by Pieter van der Westhuizen. However, I do not know how to fill an array of cells. Value in the Change event. Vba highlight cell based on value - Best answers; Vba highlight cell - Best answers; Changing a cell value based on the color of a - Forum - Office Software; Conditional formatting: change fontcolor based on another cell - Forum - Excel; Change color to different cells based on another cells text. Formula to Find the Cell Address. I turned to my old friend VBA, and started to tinker. You'll definitely want a VBA routine to do something like this. Activate Range(Cells(1, 1), Cells(5, 3)). Dear Expert, Please help this Excel VBA noob with conditional formatting. But you know it's not that easy. Conditional Formatting allows you to format a cell (or a range of cells) based on the value in it. Find method that requires some amount of understanding. She wants to select a name from a list and then display the person's email address. Explanation: the MATCH function reduces to =MATCH(12,A:A. Retrieving Values Using References Based on Row and Column Numbers in Microsoft Excel 2010. Using Range. 9 then make it green, and if the cell value divided by the same constant cell is less than 0. So far, we've looked at how to find out the address of a cell in our worksheet. However, you cannot concatenate a range of cells by referencing the range in the CONCATENATE function. Copy the value from the variable into the cell on the right. Excel is represented in a grid format, tabular form in rows and columns, so to reach to particular cell you need tell to compiler the cell is located in which row and which column in that particular row. Link that formula to a cell and suddenly it is possible to set the chart axis based on a cell value. Refer below snapshot; Here, you can see the formula bar shows selected cell i. Find is a very powerful option in Excel and is very useful. This is the way by which we can get the value of the last non-blank cell in a column in Microsoft Excel. Set cell value with For Loop: 3. VBA find duplicate values in a column Excel Macros Examples Codes: to find all duplicate records in a column in MS Excel 2003, 2007, 2010, 2013. Name ranging in excel sheet means giving a name to a Range to refer it by the name given. Please let me know the excel formula of how to extract the entire row based on one single cell value. Count statement returns a count of all the rows in the worksheet. This is easy enough with the LOOKUP() function in Excel. The Microsoft Excel FIND function returns the location of a substring in a string. Copy the code in a Standard module of your workbook, if you just started with VBA see this page. Hello, Here is what I am trying to do with this piece of code. Excel's great conditional formatting capabilities allow you to change the formatting of cells based on the content of a cell. Is there a way I can split that result in to row and column values so I can manipulate them in code? I'd like, for example to add 14 rows to the selected cells row value. Excel offers a number of ways to find rows that contain duplicate values. I have two Excel worksheets with names BATBC and GP. In the downloadable tutorial on Excel macros (VBA) you will find everything that you need to develop simple or very complex userforms. This example looks at how to use the MATCH function if your list doesn't contain any duplicates. The data for this example is a list of people's names. (See strings. With Worksheets(1). I suggest adding an auxiliary column to your worksheet that returns TRUE if the email is not a duplicate. We’ll also take a look at the opposite situation where you want to find the 2nd smallest value in a range, for example. Hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications window. Now we can look at how to use it to find out the value in that cell. Enter 0 for an R1C1 style such as R12C6 (this is cell F6), or enter 1 for an A1 style which is the classic F6. Value = 30 Setting the value of a variable based on the value of a cell 'Setting a variable based on a cell value Dim CellValue As Integer CellValue = Cells(1, 1). This is easy enough with the LOOKUP() function in Excel. The ADDRESS function is a built-in function in Excel that is categorized as a Lookup/Reference Function. Use the AutoFill function by specifying the destination range: 10. This Excel tutorial explains how to use the Excel FIND function with syntax and examples. Excel VBA custom Function Find the first colored Cell value Posted on February 26, 2015 March 21, 2017 by Wyman W This tutorial explains how to create an Excel VBA custom Function to find the first colored Cell value in a Range using ColorIndex Property. Great! >[But] in a function [neither line works]. Excel Experts, Formula for file path You can use the excel function call CELL("filename") to retrieve something that should be the file name, but for some odd reason isn't. VBA code to hide or unhide rows based on a cell value Browse other questions tagged microsoft-excel vba cells or ask your Excel - find MAX value in multiple. "color" Returns 1 if the color is a negative value; Otherwise it returns 0. The value will occur only once in the lookup search array. Address Do Set rng. Cell value will be either zero or 2 both a result of a formula in the cell. VBA code to hide or unhide rows based on a cell value Browse other questions tagged microsoft-excel vba cells or ask your Excel - find MAX value in multiple. Hey, I try to write a vba function to find the cell address for a particular cell that match the search criteria. Of course, you will have to craft the Find dialog box parameters to be specific enough. With Worksheets(1). Example 1: Select a single cell and refer a whole range of cells. Most businesses don’t have time to waste sorting through rows and rows in Excel in order to find the highest and lowest values of revenue, sales, or other information. In VBA prior to Excel 2K use application. For example Range ( "A1" ). For a simple situation shown below, VLOOKUP doesn’t seem to work (directly). If you look at the title of this article, it almost sums up what Microsoft Excel is. XLOOKUP function. If you want to play along as we proceed, now is a good time to open Excel and the VBA editor (ALT+F11) and insert a new Userform. Excel makes it easy for users to format a cell based on the value of that cell, and the built-in conditional formatting rules use this logic. We can use MAX, MATCH & ADDRESS functions to get the output. Note that FIND in VBA is similar to the FIND shortcut (Ctrl+F); whereas InStr in VBA is similar to the FIND function in Excel. 6 KB) To find the last used row in a column, this technique starts at the last cell in the column and goes up (xlUp) until it finds the first non-blank cell. In column B i have the physical path to those files. To delete a cell: Right-click the cell and click Delete. Value = i * j Next j Next i End Sub Related examples in the same category. Example: In cell A1 I have 500 In cells B1:B10 I have 100 900 100 500 400 700 200 600 200 300 I want to start in B10 and move up then stop in the first cell that is equal to or greater than what is in. Get cell value: 7. Set cell value with For Loop Sub RangeObjects() Dim i As Integer , j As Integer For i = 1 To 10 For j = 1 To 5 Cells(i, j). Cells(Counter. Excel VBA Find - How to find any value in a range of cells with VBA Nov 26, 2017 by Archana Oswal in Data Processing Many times as a developer you might need to find a match to a particular value in a range or sheet, and this is often done using a loop. Loan Calculator Excel Template. Sheet_text: The name of a worksheet to use in the reference. Cell Coloring in VBA based on value in column I am having a real tough with this one: I am wanting to set the back color of an entire row such as A2:F2 based on the value in the "F" column of that row. Why? Because there are 65,780 possible five-character combinations and only 65,536 rows in which to place them. One instance where this may be necessary is for a company that receives a check for outstanding accounts receivable (A/R) bills but doesn't have matching documentation indicating. VBA, Write Excel Values to Word Document Aug 31, 2015 by azurous in Automation In this article I will explain how you can write data from an excel workbook to a word document. It contains code examples for almost anything y ou want to do with VBA Find. re: then based on that result select the corresponding cell in the same coulomb but in Row1. Excel's great conditional formatting capabilities allow you to change the formatting of cells based on the content of a cell. The ADDRESS function is a built-in function in Excel that is categorized as a Lookup/Reference Function. In the project explorer, find Sheet1 and double click it. 9 KB) VBA Macro Code Sample: Hide All Columns Containing a Value. Substitute probably can be greatly reduced based on the technique used above. An object is a special type of variable that contains both data and codes. The Value property returns the actual value of the cell. It helps to create an array and store elements in an array. Is there a way I can split that result in to row and column values so I can manipulate them in code? I'd like, for example to add 14 rows to the selected cells row value. Highlighting certain rows based on a certain value manually will take a lot of time. To display the reference style of the cell address. The following macro will hide all the column containing an X in each cell in row 1. For example, you can highlight a particular cell based on a condition. You can simply copy-paste them in your VBA editor and use them. ) largest number in a range. Cells, where expression is an expression that returns a Range object, to obtain a range with the same address consisting of single cells. Therefore, we are basically specifying the last cell in column A of the sheet. This has been a guide to VBA Find Function. Read More… Read Values from Range to an Array. looping through a worksheet range using a For/Next loop. To retrieve the values using references based on row & column numbers, we will use a combination of "INDIRECT"& "ADDRESS" functions to get the output. 5 thn make it orange. cells(row, col) instead of activesheet. In the VBA programming language, the minus sign (-) is used to subtract one value from another. The indirect() function ";returns the reference specified by a text string". : ActiveCell « Excel « VBA / Excel / Access / Word. Hidden) And Not. In this MS Excel tutorial from ExcelIsFun, the 730th installment in their series of digital spreadsheet magic tricks, you'll learn how to create a formula that will retrieve the cell address of the minimum value in column. The problem is that the number of cells to be summed will vary; for one run of the macro it could be 100 cells, while on the next it could be 300 and on the third only 25. This VBA macro code allows you to select a cell with a specific fill color and it will automatically determine the last cell within your spreadsheet containing the designated fill color. This sub also runs when connected with a button, so you might have to put an autorefresh sub in if you want it go automatic but it can also slow down your workbook. Excel VBA Find: Step by Step Guide and Examples to Find Values, Strings, Characters with Macros By J. In fact, it might even be faster to use this instead looping through multiple cells or rows in VBA. If we click on cell B3 then, the row will be highlighted in yellow color. Address Do Set rng. com/mdocs-posts/looping-through-. Value = 30 Setting the value of a variable based on the value of a cell 'Setting a variable based on a cell value Dim CellValue As Integer CellValue = Cells(1, 1).