Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
conditional formatting text
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
conditional formatting text
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 kate wrote: 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
conditional formatting text
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 -- Allllen "kate" wrote: 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
conditional formatting text
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 -- Allllen "shail" wrote: 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 kate wrote: 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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
conditional formatting text
'----------------------------------------------------------------- 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) "kate" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Formatting when inserting a row | Excel Worksheet Functions | |||
Conditional formatting : amount of decimals | Excel Discussion (Misc queries) | |||
Formulas dealing with text data | Excel Worksheet Functions | |||
How do I apply conditional formatting to a text box | Excel Worksheet Functions | |||
Conditional Formatting based on Text within Text | Excel Discussion (Misc queries) |