Excel VBA for Conditional Formatting 1

[PDQBach]

I regularly distribute spreadsheets to a number of people and take their updates, consolidate them and prepare a report.

To quickly spot updated fields, I take (for example) columns A-E and copy them to AA-AE, then apply conditional formatting on columns A-E so that if the cell (for example) B5 does not match AB5 then the B5 cell is highlighted in bold red.

Here's the VBA that I use:

    lngRowLast = Cells.SpecialCells(xlCellTypeLastCell).Row
    lngColLast = 5 ' Adjust to reflect number of actual data columns

    For lngRow = 2 To lngRowLast 'Row 1 has headings
        For lngCol = 1 To lngColLast
            Cells(lngRow, lngCol).Select
            Selection.FormatConditions.Delete
            Selection.FormatConditions.Add _
                Type:=xlCellValue, Operator:=xlNotEqual, _
                Formula1:="=A" & Mid("ABCDEFGHIJKLMNOPQRSTUVWXYZ", lngCol, 1) _
                        & CStr(lngRow)
                With Selection.FormatConditions(1).Font
                    .Bold = True
                    .Italic = False
                    .ColorIndex = 3
                End With
        Next
    Next

Generally speaking, this works well and handles strings, numbers, and dates. The only problem comes when someone "messes about" with the contents of columns AA-AE.

Is it possible to set up the VBA so that, instead of referencing the offset column, I can actually plug into the formula the current value. I can do this Format > Conditional Formatting ... and typing in a constant (i.e., the value that is currently in the cell) but this manual approach simply isn't practical for more than a handful of cells.

Constraints: I need to code to handle numbers, strings, dates and blank cells. By convention, if my users want to clear out a value in the cell and change the cell from something to nothing then they put in "DEL" so I can see that it has changed.

Additional: If you can suggest VBA code that works on a column at a time or on the full range of cells, that would be an excellent learning for me.

 

[SkipVought]

PDQBach,

I've enjoyed browsing your responses in the MS Project forum, and I have played a few pieces from 'PDQ Bach' ;-)

Here's all the code you need...

'select the range to conditional format
    [AA1].CurrentRegion.Select

'format the UPPER LH cell only, and VOLA y'all!
    With Selection
        .FormatConditions.Delete
        .FormatConditions.Add _
            Type:=xlExpression, _
            Formula1:="=AA1<>A1"
        With .FormatConditions(1).Font
            .Bold = True
            .Italic = False
            .ColorIndex = 3
        End With
    End With

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[PDQBach]

Thanks, Skip! That will certainly work keeping the data in AAn through AEn (where "n" is any row number).

But is it possible to bypass storing data in the AA-AE columns and, instead, simply loop through the cells in An-En, visiting each cell in turn and picking up the existing value and then putting it directly (as a constant) into the Conditional Formatting formula for that cell?

If that's not possible (since I want to be able to handle numbers, dates, strings, etc.) then your improvements on my code are definitely appreciated.

 

[SkipVought]

What would you compare A1 to?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[PDQBach]

I'd use VBA to get the existing value in A1 and then create a Conditional Formating formula comparing cell A1 to a constant (where that constant is, actually, the original value in A1).

Doing it manually ...

Assume that cell A1 has the value "ABCdef"

Click on cell A1
Format > Conditional formatting...
In the popup:
Choose: Cell Value Is
Choose: Not equal to
Type: ABCdef
click on the Format button
choose: Bold
choose: Red
click on OK
click on OK

If I change the value of cell A1 from ABCdef to anything else, the cell contents are displayed in bold red.

If I click on cell A1 and then click on Format > Conditional formatting...

The popup shows:
Cell Value Is
not equal to
="ABCdef"

 

[SkipVought]

How do you know what the original value was in EACH AND EVERY CELL IN COLUMNS A to E?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[PDQBach]

The original values come from the spreadsheet that I have created and am ready to distributed.

For the purposes of this discussion assume that column A contains names, column B has ages, and so on. By looping through the rows and columns, I would pick up the existing data in each cell and use it as a constant in a CF formula, and then move to the next cell.

When the user gets the spreadsheet, he sees columns A to E with data which he changes or leaves alone. The cells where he changed the data will show the new data in bold red because it is not the same as the constant in the underlying CF formula for that cell.

 

[SkipVought]

You would be a whole lot better off, copying the SHEET (not the data) and HIDING the sheet. Lets say it is Named as MyCopy.

Then in the Worksheet_Change event, evaluating...

Private Sub Worksheet_Change(ByVal Target As Range)
    With Target
        If .Value <> Sheets("MyCopy").Cells(.Row, .Column).Value Then
            With .Font
                .Bold = True
                .ColorIndex = 3
            End With
            .Interior.ColorIndex = 38
        Else
            With .Font
                .Bold = False
                .ColorIndex = xlAutomatic
            End With
            .Interior.ColorIndex = xlNone
        End If
    End With
End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[PDQBach]

That makes sense. Thank you for walking through this one with me. Definitely appreciated.