Forum Discussion
How do we apply conditional formatting icon sets to a single contiguous or non-contiguous row?
PROBLEM: To evaluate the data in a single row (contiguous or non-contiguous cells in the row) and assign each an icon, we must create a conditional format rule for each row. Not only impractical, but incomprehensible that this could be the case. A table with 2,000 rows would require 2,000 individual conditional formatting rules! Somehow, we must be missing something here in our research.
EXAMPLE: We have created a special rule for row-1 that displays a red, yellow, or green dot for each cell based on the values in just that row. This works great for a single row. However, we see no way to copy and paste the conditional formatting from row-1 to subsequent rows. FIRST, we cannot because the cell or range references cannot be relative in conditional format formulas, so they do not adjust when copied down to subsequent rows, even if using the format painter. SECOND, we cannot apply the conditional format to a range of rows because the data analysis is now based on all the values in the whole range and not each row.
SOLUTION: Does anyone have a solution? We're hoping this is just an obvious oversight on our part. A great solution (perhaps the perfect solution) would be having the ability to use relative cell references in conditional formatting formulas, just like in cells, but that is not allowed.
Thank you for any assistance!
1 Reply
1. Apply to a continuous range of cells
Select the contiguous cell range (e.g. A1:A10) to which you need to apply the icon set.
Click “Start” → “Conditional Formatting” → “Icon Sets” and select the desired icon style (e.g. red, yellow and green arrows).
Adjust rules (optional):
Click “Manage Rules” → “Edit Rules” → modify the threshold and type (e.g. percentage, numeric value, etc.).
2. Apply to non-contiguous cells (multiple scattered cells)
Using the formatting brush (for a small number of cells)
Apply icon set conditional formatting to one cell first.
Select the cell → Click “Format Brush” (Ctrl+Shift+C) → Brush to other non-contiguous cells.
3. Use formula rules (recommended batch processing)
Select all target cells (hold Ctrl to multi-select).
Click “Conditional Formatting” → “New Rule” → “Use Formula to determine the cells to be formatted”.
Enter the formula (example):
Assume to determine whether A1 is greater than the median:
excel
=A1>=MEDIAN($A$1:$A$10)
Click “Format” → Select Icon Set → Set Rule Priority (to avoid conflict).
4. VBA batch application (suitable for complex needs)
vba
Sub ApplyIconSetToNonContiguous()
Dim rng As Range
Set rng = Union(Range(“A1,A3,A5”), Range(“C2,C4”)) 'Modify to your non-contiguous area
rng.FormatConditions.AddIconSetCondition
With rng.FormatConditions(1)
.SetFirstPriority
.IconSet = ActiveWorkbook.IconSets(xl3Arrows) '3 color arrows
End With
End Sub
(Press Alt+F11 to open VBA editor, paste the code and run it)
