Excel - VBA

[michaelm702]

Hi,

I have a excel spreadsheet which has a column in which the cells will contain code values seperated by comma. For eg. M = Male, F = Female
I want to highlight cells in the column which do not have values in this format. ( Format is "Code = Value, Code = Value" ) i.e multiple codevalues should be seperated by comma and each pair of code value should be seperated by "=".

[geekgirlau]

This code assumes that:

• the cells containing the codes are in a range named "rngCode";
• the next 5 columns to the right are empty
• the next 4 columns to the right are probably hidden

[vba]
Range("rngCode").Copy Range("rngCode").Offset(0, 1)
Range("rngCode").Offset(0, 1).TextToColumns _
DataType:=xlDelimited, Comma:=True, Other:=True, OtherChar:="="

With Range("rngCode").Offset(0, 5)
.FormulaR1C1 = "=COUNTA(RC[-4]:RC[-1])"
.FormatConditions.Delete
.FormatConditions.Add Type:=xlCellValue, Operator:=xlNotEqual, Formula1:="4"

With .FormatConditions(1).Font
.Bold = True
.ColorIndex = 3
End With

.FormatConditions(1).Interior.ColorIndex = 3

.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="4"
.FormatConditions(2).Font.ColorIndex = 2
End With
[/vba]