Conditional Formatting - cFormat Macro Code

[Commcast]

Hi Guys,


I was wondering if I could get a hand.


I've been trying to modify the code provided on the page http://chandoo.org/wp/2008/10/14/more-than-3-conditional-formats-in-excel/ so that instead of using numbers in the conditions2use part, it uses text.


E.g. Instead of 1 its BP, 2 is CM, 3 is CR etc.


I've spent about 2 hours trying to figure out how to do it, but I either get mismatch type errors or I break it and it doesn't run.


Not sure what else to try really. Any help would be appreciated and basically I'm pushing this method because the workbook is being run on Office 2003 and my data is exactly like the example except instead of numbers its text.


Again, any help would be appreciated.


Cheers.

 

[Hui]

Commcast


Firstly welcome to the Chandoo.org Forums


I have been using the following small piece of c ode for years to do what you require


Setup 2 Named Formula:

Data : Your data area

Format : A column of values of what can appear in your data area. Each cell can have a format that will be applied to that value.


Copy the following code into a code module and execute it

[pre]

Sub Format()
Dim formats As Variant
Dim i

formats = Range("formats").Value
LF = LBound(formats, 1)
UF = UBound(formats, 1)
For Each cell In Range("data")
For i = LF To UF
If cell.Value = formats(i, 1) Then
Range("formats").Cells(i, 1).Copy
cell.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
End If
Next
Next
Application.CutCopyMode = False
End Sub

[/pre]

 

[Commcast]

Hi Hui,


Thanks for your help. I see what your code does but I need to tweak it a little bit.


In your example, if I relate it to the original macro, it uses conditions2use to find where to format to and then formats the corresponding cell in data2use. In your code you are directly formatting the conditions part.


Not sure if that makes sense or not.

 

[Hui]

I didn't look at that code, I looked at your problem and gave you what I used to use with 2003.


In my code I go through each cell in the data area

If the value of the cell (number or text) is in the format area, then take that format and apply it to the data cell


In Chandoo's code, you can put any formulas you want in the Conditions area to return a value between 1 and 9 and the corresponding format will be applied to the Data Area

eg: in X5 put =IF(left(B5,1)="A",3,5) and copy across and down to the Conditions area

This will format any cells starting with A as Format no 3 all othrs will be Format no 5

 

[Commcast]

I see what you mean. So in your code it looks to match exacts I think.


My Format list for instance has BP, CM.


My data would have BP Plant1, CM Plant 1. Anyway I could have it still match say the first 2 or 3 characters.


Also Hui thanks for help me out, you've been a life saver and I'm learning heaps.

 

[Hui]

So in X5 put

=IF(left(B5,2)="BP",1,IF(left(B5,2)="CM",3,5)

etc

copy down/across