seattletimebandit
Board Regular
- Joined
- Apr 11, 2013
- Messages
- 69
I'm looking to write a macro that loops through a table to compare data and make format changes.
I have a table that I need to compare data in cells in a Range (by selecting the range) to data in other cells (all in a single Column down to x rows). If the data in the Selected Range is ">=" the data in the adjacent Column C, then the cells in the Range that meet certain criteria are highlighted.
The criteria are that if the cell is empty or if there is some text, ignore it, only numeric values need to be highlighted.
I've tried to "talk" it out by the following:
The following snippet works when selecting a row and comparing the cells in the selection to a specific cell ($B3). Some the data in cells are numbers:
1.25, 5.13, etc. and some have a letter 0.500 U, 1.00 U. I'm ignoring the cells with "U's".
I just need to loop through a row, meet the criteria, then loop through columns, meet the criteria, then start again on next row to the end.
Thanks in advance!
I have a table that I need to compare data in cells in a Range (by selecting the range) to data in other cells (all in a single Column down to x rows). If the data in the Selected Range is ">=" the data in the adjacent Column C, then the cells in the Range that meet certain criteria are highlighted.
The criteria are that if the cell is empty or if there is some text, ignore it, only numeric values need to be highlighted.
I've tried to "talk" it out by the following:
- Check to see if there are any numbers in the cells in column B (starting in B3 down to B100), if the cell is empty, continue down each row in column B until a number is found.
- if a numeric value is found in the cell in Column B, then starting checking cells in the Selected Range, moving to the right (Column 3 through Column 103) in that same row, searching for numeric values to the end of row (ending at Column 103)
- if a cell is blank or has text (non-numeric) keep moving along the row to the end
- if cell has a numeric value, compare it to the value found Column B (in that same row) do the following
- if value is "<" the column B value, ignore it
- if cell value is ">=" to the Column B value, change cell color (I have a bit of formatting code for that)
- After checking and changing the cells with values in the row, move to next row starting back in Column C
- repeat above until no data found (col 100, row 100)
The following snippet works when selecting a row and comparing the cells in the selection to a specific cell ($B3). Some the data in cells are numbers:
1.25, 5.13, etc. and some have a letter 0.500 U, 1.00 U. I'm ignoring the cells with "U's".
I just need to loop through a row, meet the criteria, then loop through columns, meet the criteria, then start again on next row to the end.
Thanks in advance!
Code:
Sub ChangeCellColor()
Dim c As Range
For Each c In Selection
c.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, Formula1:="=$B3"
If Right(c.Value, 1) <> "U" Then
With c.FormatConditions(1)
.Interior.PatternColorIndex = xlAutomatic
.Interior.Color = RGB(197, 217, 241)
.Font.ColorIndex = 1
End With
End If
Next c
End Sub