CODEWITHSUNDEEP
vbaexcel
Helloguys
Helloguys

Reputation: 289

Conditional Formatting - how to reference a cell itself?

I was trying to write VBA code to set conditional formatting for a column, so that when the cell contains mix of lower-case and upper-case letter, fill the cell with yellow color. Column number will be determined by variable c. And last row number will be determined by variable last_row.

The following code didn't work as desired. It looks like LCase(xlCellValue) always returns "1" instead of the cell content in lower case format. Thus the condition becomes "CellValue <> 1", which didn't do what I wanted it to do.

c = 1
last_row = 10
Range(Cells(1, c), Cells(last_row, c)). _
  FormatConditions.Add(xlCellValue, xlNotEqual, LCase(xlCellValue)). _
  Interior.ColorIndex = 6

So I guess I'll have to use xlExpression instead of xlCellValue. But how shall I write the formula so it'll take variables c and last_row? Thank you!

Upvotes: 1

Views: 557

Answers (2)

Mistella
Mistella

Reputation: 1738

Here, try this:

Sub test()
c = 1
last_row = 10
Range(Cells(1, c), Cells(last_row, c)). _
  FormatConditions.Add(xlExpression, , "=NOT(Exact(" & Cells(1, c).Address(RowAbsolute:=False) & ",Lower(" & Cells(1, c).Address(RowAbsolute:=False) & ")))"). _
  Interior.ColorIndex = 6
End Sub

I don't know if the comparison with the xlCellValue, xlNotEqual would be case-sensitive; however, Excel has a function Exact which is a case-sensitive comparison. I added a Not to cause the return from the formula to be true when the string is not all lower case. Since this is set-up using Excel Functions, I also replaced Lcase with Lower.

Note:

Using the logic you were following, if the entire string is upper-case, it also gets colored yellow. If you only want the conditioning when it's a mix of cases, you should use the below code instead:

Sub test()
c = 1
last_row = 10
Range(Cells(1, c), Cells(last_row, c)). _
  FormatConditions.Add(xlExpression, , "=NOT(OR(Exact(" & Cells(1, c).Address(RowAbsolute:=False) & ",Lower(" & Cells(1, c).Address(RowAbsolute:=False) & ")),Exact(" & Cells(1, c).Address(RowAbsolute:=False) & ",Upper(" & Cells(1, c).Address(RowAbsolute:=False) & "))))"). _
  Interior.ColorIndex = 6
End Sub

Upvotes: 1

Mathieu Guindon
Mathieu Guindon

Reputation: 71217

You're adding the format condition to [ActiveSheet.]Range([ActiveSheet.]Cells(1, c), [ActiveSheet.]Cells(last_row, c)) - you can extract that into a local variable:

Dim target As Range
Set target = ActiveSheet.Range(ActiveSheet.Cells(1, c), ActiveSheet.Cells(last_row, c))

And then use that variable to refer to the cell:

Dim fCondition As FormatCondition
Set fCondition = target.FormatConditions.Add(xlCellValue, xlNotEqual, LCase$(target.Value))

It looks like LCase(xlCellValue) always returns "1" instead of the cell content in lower case format.

That's because xlCellValue is a constant, with a value of 1. LCase(xlCellValue) is therefore equivalent to LCase(1), which will always only ever return "1".

Upvotes: 0

Related Questions