CODEWITHSUNDEEP
vbaexcel
Helloguys
Helloguys

Reputation: 289

Conditional Formatting - Number stored as text

I tried to use VBA to add conditional formatting to a column, so that when the cell value not equal to a reference value, fill the cell with yellow color.

' c represent the column number, lRow represent the last row
' RefVal is a string variable read from reference spreadsheet

Range(Cells(2, c), Cells(lRow, c)).FormatConditions. _
    Add(xlCellValue, xlNotEqual, RefVal).Interior.ColorIndex = 6

After running the code, the condition rule is as below: enter image description here

What I really want is below (note the quotes around 183957): enter image description here

It was because the cells I'm working on are "number stored as text". If I use the first condition rule, it will always be "true". Only the second rule can do the real compare.

enter image description here

How shall I solve this problem? I have no control of the spreadsheet contents. How shall I compare "text to text" vs. "text to number"? Or is there an easier solution?

Upvotes: 0

Views: 436

Answers (1)

Michał Turczyn
Michał Turczyn

Reputation: 37337

You can do that entirely in VBA:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Application.EnableEvents = False

    Dim lastRow As Long, i As Long, RefVal As String 'maybe other appropriate datatype
    RefVal = Worksheets("ReferenceSheetNameHere!").Cells(1, 1).Value 'in Cells, specify coordinates of a cell with reference value!
    lastRow = Cells(Rows.Count, 1).End(xlUp).Row

    For i = 1 To lastRow
        If Cells(i, 1).Value <> RefVal Then
            Cells(i, 1).Interior.ColorIndex = 6
        Else
            Cells(i, 1).Interior.ColorIndex = 0
        End If
    Next

    Application.EnableEvents = True
End Sub

Note that (as mentioned in comments in code) you have to specify where your reference value is located.

This method with this signature handles worksheet change event including cell change, so it mimics autofilter, but is more powerful.

In order to make it work, this code should be pasted in VBA file corresponding to sheet you want to handle, i.e. when you open VBA editor, on the left side in the tree you should select the sheet.

Upvotes: 1

Related Questions