conditional formatting text

K

kate

Could you please help me?
I am trying to format text to highlight different colours depending on code
e.g

cds (I would like to be blue) and vgh ( I would like to be blue)
bn (I would like to be red)
cda ( I would like to be orange) and khg (I would like to be orange)

and so on...
The data range is c1:c550 with various names
hope you can help thankyou Kate
 
S

shail

Hi Kate,

Do the following:

1. Go to Formats/Conditional Formatting
2. "Cell Value Is" selected, select drop down box next to it as "Equal
To"
3. Next box type as "cds"
4. Click Format button
5. Choose the thing you want to as - if you want the font to be
coloured or background to be coloured. Select.
6. For next word "vgh", click at "Add" button.
7. Repeat steps 2 to 5

After completing your steps click OK to finish. Then Copy the cell
where you have done the conditional Formatting. Select the data range.
Right click your mouse button, select paste special/Formats.

Hope this will help you.

Thanks,

Shail
 
A

Allllen

Hi Kate,

I have written you a program to help you out. You will need to paste this
into a module in the Visual Basic Editor. Press <Alt-F11> to open the VB
Editor, go to View > Project Explorer (don't worry if nothing happens because
maybe you can already see it), then look for a project called "VBAProject
(YourWorkbookName)". Right click on this and do Insert > Module. You will
see a blank page on the right hand side. Copy the text below and paste it in
there.

Sub Kate()
For x = 1 To 550
If Cells(x, 3).Value = "cds" Then Cells(x, 3).Font.ColorIndex = 5
If Cells(x, 3).Value = "vgh" Then Cells(x, 3).Font.ColorIndex = 5
If Cells(x, 3).Value = "bn" Then Cells(x, 3).Font.ColorIndex = 3
If Cells(x, 3).Value = "cda" Then Cells(x, 3).Font.ColorIndex = 3
If Cells(x, 3).Value = "khg" Then Cells(x, 3).Font.ColorIndex = 45
Next x
End Sub

Now go back to the excel window and do Tools > Macro > Macros and do the one
called Kate.

I think you will see quite easily how to modify the code above to give you
what you need. You can add more conditions in there if you want just by
copying the lines. If you want to change the fill on the cells instead of
the font colour, use the word Interior instead of the word Font.

Here is another program you can use in the same way which will just give you
the numbers you need for each of the main colours, in case you want to choose
other ones.

Sub colourfinder()
Dim x As Integer, writerow As Integer, writecol As Integer
x = 1
writerow = 1
writecol = 1
Do Until x = 57
For writecol = 1 To 8
Cells(writerow, writecol).Value = x
Cells(writerow, writecol).Interior.ColorIndex = x
x = x + 1
Next writecol
writerow = writerow + 1
Loop
End Sub
 
A

Allllen

This works as long as you only want a total of 4 different colours (1 base
and 3 defined by conditional formatting).

Kate if you want to have both "cds" and "vgh" turning blue, use
Formula is =OR(A1="cds",A1="vgh")

rather than cell value is

HTH please rate
 
B

Bob Phillips

'-----------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------
Const WS_RANGE As String = "H1:H10" '<==== change to suit

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Select Case .Value
Case "cds": .Interior.ColorIndex = 5 'blue
Case "vgh": .Interior.ColorIndex = 5 'blue
Case "cda": .Interior.ColorIndex = 46 'orange
Case "khg": .Interior.ColorIndex = 46 'orange
Case "bn": .Interior.ColorIndex = 3 'red
'etc.
End Select
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
Top