Conditional Formatting in VBA

[plawrenz]

I need to write vba code for an excel spreadsheet that shows condtional formatting for items that are not the same. The spreadsheet below has duplicate rows and in the case below the bond doesn't match the corporate bond I need to highlight that with conditional formatting. There is a space in between each set. so for example:


Please let me know how I would do this through looping. Thanks

[mbarron]

A sample workbook would be a better option than the example you've posted. Don't make people recreate something you've already done.

If your data is consistent - such as two rows of data followed by a blank row - you don't need to use a macro. Format the two rows the way you want, highlight the two rows and the blank row. Copy those 3 rows. Highlight the rest of the data, choose Edit - Paste Special... choose Formats.

You have to format the rows separately. The formulas will be the same, but because for relative referencing, they need to be set separately.

[plawrenz]

The data is not consistant there could be 2 duplicate rows there could be 5 dup rows, there could be 15 dup rows in a set and the spreasheet is 10,000s of line long, so I am in need of a looping code to find blank rows and then do conditional formatting from the previous blank line. So if someone would please help and not be rude since I am pretty good at excel and coding and I am running into issues trying to do this. Thanks!

[mbarron]

Please post a sample book.
How are the duplicate rows to be handled? The two row is obvious A<>B then highlight, but what about situations where you have 10 rows? If one of the rows is different, do all rows get highlighted?

[plawrenz]

The whole group can be highlighted or just the one. I just need to call out the differences. I attached a better example. Thanks!

[SamT]

Here's my .02 worth of pseudocode

Insert a row above Row 2

Set BeginRange to first empty row
Set EndRange to next empty row

Set WorkRange = EndRange - BeginRange -1
Test for inequality
Set Fill color

Set BeginRange = EndRange
Set New EndRange
loop

Next Column



SamT

[plawrenz]

Thanks for the start and I sort of had something like that, could you give an example of code that I could cut and paste, I am just running into syntax issues too. Thanks

[mbarron]

Code assumes used columns are A through D and row 2 is blank.

Sub condformat()
Application.ScreenUpdating = False
Range("a1").Select
Dim lRow As Long, i As Long, myRange As Range, nB As Long, j As Long, strRng As String, strCond As String
lRow = Cells.Find(what:="*", LookIn:=xlFormulas, SearchOrder:=xlByRows, searchdirection:=xlPrevious).Row

For i = lRow To 3 Step -1
nB = Cells(i, 1).End(xlUp).Row
Set myRange = Range(Cells(nB, 1), Cells(i, 1))
strRng = myRange.Address(1, 0)
For j = i To nB Step -1
With Range(Cells(j, 1), Cells(j, 4))
.FormatConditions.Delete
strCond = "=countif(" & strRng & ",A$" & j & ")<>counta(" & strRng & ")"
.FormatConditions.Add Type:=xlExpression, Formula1:=strCond
.FormatConditions(1).Interior.ColorIndex = 34
End With
Next
i = nB - 1
Next
Application.ScreenUpdating = True
End Sub

[plawrenz]

Thanks exactly what I was looking for! You saved me soooo much time!!!

[mbarron]

I realized that the code doesn't need to go from bottom. This version assumes A through D are the columns and there is no gap between the header rows and the first data row.

Sub condformat2()
Application.ScreenUpdating = False
Range("a1").Select
Dim lRow As Long, i As Long, myRange As Range, nB As Long, j As Long, strRng As String, strCond As String
lRow = Cells.Find(what:="*", LookIn:=xlFormulas, SearchOrder:=xlByRows, searchdirection:=xlPrevious).Row

For i = 2 To lRow
nB = Cells(i, 1).End(xlDown).Row
Set myRange = Range(Cells(nB, 1), Cells(i, 1))
strRng = myRange.Address(1, 0)
For j = i To nB
With Range(Cells(j, 1), Cells(j, 4))
.FormatConditions.Delete
strCond = "=countif(" & strRng & ",A$" & j & ")<>counta(" & strRng & ")"
.FormatConditions.Add Type:=xlExpression, Formula1:=strCond
.FormatConditions(1).Interior.ColorIndex = 34
End With
Next
i = nB + 1
Next
Application.ScreenUpdating = True
End Sub