Conditional Formatting For Another Cell Excel VBA Macro

In a previous post we saw how to apply conditional formatting rules in Excel with VBA macros. In that example, formatting was applied to a column based on unique cell values, thus highlighting each unique cell value with the same color. In this post we see how to apply conditional formatting to a column based on the value in another column. We can tweak the code slightly to highlight the entire row as well.

 

Sort Data With Custom List Excel VBA Macro

Microsoft Excel allows to sort a range based on cell values, cell and font color, and conditional formatting, in ascending or descending order, or using a custom list. In this post we see how to create a custom list of priority categories to sort a range with data accordingly. Excel includes built-in custom lists for weekdays and months. This macro example adds another custom list that is used temporarily to sort the range values. After sorting the table, the custom list is deleted.

Add Consecutive Numbers Excel VBA Macro

In this post we see how to add consecutive numbers to cells in the Excel worksheet using VBA macros. We review two simple options: 1. Using an classic programming For loop, and 2. Using the AutoFill function (a method of the Range object). Using AutoFill requires to add the first 2 numbers to set the sequence of the following numbers (option 2a), or adding just one number and choosing to auto fill with a series (option 2b).

Clear Cells Based On Value Excel VBA Macro

There are several ways to clear cells based on cell value with Excel VBA. The easiest, and probably also most efficient way to do it is using the Replace method of the Range object, which can replace the target value with an empty string in a given range or entire worksheet. Another possibility is looping through all cells in a range and setting a condition to clear the contents for a target value. This gets slower the bigger the range. On the other hand, this method allows not only to clear values, but also to change formatting or delete the target cell, for example. It also allows to set various conditions, instead of a single value.

Add Conditional Formatting To Unique Values Excel VBA Macro

Conditional formatting is a feature of Microsoft Excel that allows highlighting certain values to easily identify those cells. Excel offers various options for numerical values (greater or less than, between, top 10, data bars, color scales, icons, etc.), but there are less options for text. In both cases, we can set rules manually to apply formatting based on a given condition. This post shows how to apply rules automatically using Excel VBA macros. The macro specifically applies formatting to a column based on unique cell values. As a result, each unique cell value is highlighted in the same color.

Delete Rows Based On Criteria Excel VBA Macro

In this post we see how to delete rows based on given criteria in Excel using VBA macros. The macro loops through each row with content in the worksheet, and checks if certain criteria for one or more fields is met to delete the row. The criteria can be any value along with the relational operators of the If statement. For example, that could be when the value is below a certain price, or when the profit is above a certain margin. It can also check for specific text, a string of certain length, etc. In the below example, we delete rows for products with a volume below 700 ml or price above 1000 (of the given currency) in the dataset.

Consolidate Filtered Data From Multiple Sheets Excel VBA Macro

In this post we see how to consolidate filtered data from multiple sheets into separate sheets for each unique entry using Excel VBA macros. The macro loops through each worksheet and filters each unique entry in column A, then adds a new sheet for each unique entry to copy/paste the data. It is a modification of a previous macro (Copy Filtered Data To New Sheets), that allows to combine data from various sheets.

Copy Columns To Separate Sheets Excel VBA Macro

In this post we see how to copy each column with data to a separate worksheet in Excel using VBA macros. This is useful when dealing with many columns that need to be moved to other sheets to have a separate record for the data in each column or maybe do some further analysis or report. The macro loops through each column in the reference worksheet and creates a new sheet with the name of the column header, while copying the rest of the data to the first column in that new sheet. The macro also sets a different tab color for each new sheet.

Stack Combine Multiple Columns Excel VBA Macro

In this post we learn to stack or combine multiple columns into a single column in Excel using VBA macros. Depending on the scenario, that’s something you can probably do without macros, but in some cases, and especially when dealing with a lot of data, is more convenient, faster, and more secure to do it with macros. This is a simple macro to stack the selected columns into one column either in the same or a different worksheet. The macro can easily be adapted to stack the columns skipping the headers by changing just one line of code (see more in the explanation below).

Strikethrough Text In Cell Excel VBA Macro

In this post we see how to strikethrough the text in a cell in Excel using VBA macros. The macro example consists of an event procedure triggered when trying to delete the content of a cell. It actually prevents deleting the value of a cell and strikes through the content instead. This somehow protects the content in a worksheet from being deleted, without using worksheet protection, and may be useful when sharing a worksheet with other users.

WARNING: The code below prevents from selecting multiple cells and deleting the value of cells in the sheet where the VBA code is added.

Group Rows With Same Format Excel VBA Macro

In this post we see how to group cells or rows with the same format in Excel using VBA macros. Grouping cells or rows in Microsoft Excel is quite helpful when dealing with lots of data arranged in categories, phases, etc., which we may need to monitor or keep updating regularly. Then, we can collapse groups when complete or when not relevant at a given time, thus making space and setting focus on the target data. The Excel VBA macro loops through the dataset, and groups rows based on the format, specifically, the example below groups rows sharing the same category or headers with bold text. That can be changed to any other format condition such as font size, color, etc.

Compare Two Columns Excel VBA Macro

In a previous post we saw how to Compare Two Worksheets in Excel using VBA macros. In this post we see how to compare two columns with data. Each column is treated as a list of items, and the goal is to compare which items or values are not in common. Thus, the macro compares the two lists or columns in the active sheet, and finds the discrepancies between the two. In this macro example, the discrepancies are highlighted in yellow and also added to another sheet. The lists are supposed to have unique entries, but the macro also works is entries are repeated. In that case though, all of them will be highlighted and also added to the other sheet, thus having repeated terms there. The macro can be easily tweaked to avoid that and show only unique entries if used to compare two columns or lists with repeated entries.

Sort And Merge Groups In Table Excel VBA Macro

In this post we see how to sort and merge a group of cells with the same value in Excel using VBA macros. Selected values are sorted in ascending order, as per the value in column A first, and then column B. Then, the groups of contiguous cells with the same value are merged.

AutoFill Table With SpecialCells Excel VBA Macro

In a previous post we have seen how to autofill table gaps in Excel using VBA macros. In this post we see another way to do it with less code. This macro example uses the CurrentRegion property to select the table, the SpecialCells property to target the gaps only, and the Offset property to get the value to be filled.

Split Text Into Columns Excel VBA Macro

In this post we see how to split text into columns in Excel using VBA macros. The macro splits the text in one column into as many columns as dictated by the text separator (a comma, space, etc). In this example, we use the TextToColumns method of the Range object. But this can also be achieved using the Split VBA function to create an array and then write into columns.

AutoFill Table Data Gaps Excel VBA Macro

Sometimes we need to manually create a table with many repeating terms and is time-consuming to use Excel’s AutoFill/FillDown or Go To Special features to fill the gaps. We can easily do that with Excel VBA. This macro example fills table gaps with values above within each column in the selected range.

Add Unique Entries Validation List Excel VBA Macro

In a previous post, we have seen how to get a list of unique entries in Excel using VBA macros. Now we are going to add a validation list with available unique entries to the selected range using Excel VBA.

 

List Unique Entries Excel VBA Macro

We can easily filter data within columns in Excel. Sometimes we need however to get the list of those unique entries Excel is showing to filter through, so here’s a macro that gets that list for us. Use the code below to get the list of unique entries with Excel VBA macros.

Highlight Duplicates Excel VBA Macro

This example shows how to highlight duplicates with Excel VBA. It uses a For Each loop to check if any value in a given range selection is repeated across the selection. In that case, it highlights all cells with the same value.

Delete Blank Rows Excel VBA Macro

Cleaning up and arranging a data set is always tedious job, but Excel macros can do the job for us. In this example we see how to delete blank rows with Excel VBA. It is a simple macro example to understand programming loops and conditional statements. Use the code below to delete blank rows in Excel.