| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Format VB Code for Begins With
Hi
I've cribbed the following code from this site to use for some conditional formatting. However, i'd like to change it so that if the cells BEGIN WITH the letters I've put into the code the formatting will work. Plus I'd also like to format the colour of the text using the same critieria, ie, if the cell begins with "PM-" then colour the cell and colour the text. I'd be grateful for any suggestions, thanks. Private Sub Worksheet_Change(ByVal Target As Range) Dim Num As Long Dim rng As Range Dim vRngInput As Range Set vRngInput = Intersect(Target, Range("A1:iv63000")) If vRngInput Is Nothing Then Exit Sub On Error GoTo endit Application.EnableEvents = False For Each rng In vRngInput 'Determine the color Select Case UCase(rng.Value) Case Is = "PM": Num = 22 Case Is = "BH": Num = 45 Case Is = "H": Num = 4 Case Is = "PE": Num = 35 Case Is = "E": Num = 5 Case Is = "R": Num = 6 Case Is = "P": Num = 29 Case Is = "A": Num = 7 Case Is = "PRE": Num = 8 End Select 'Apply the color rng.Interior.ColorIndex = Num Next rng endit: Application.EnableEvents = True End Sub -- Marie Bayes |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Conditional Format VB Code for Begins With
You'd normally use:
Left( text , no_of_characters ) to check "begins with" but in your case you have 1-, 2- and 3- character strings, so this would be more complicated. Pete On Dec 19, 2:38*pm, Marie Bayes wrote: Hi I've cribbed the following code from this site to use for some conditional formatting. *However, i'd like to change it so that if the cells BEGIN WITH * the letters I've put into the code the formatting will work. *Plus I'd also like to format the colour of the text using the same critieria, ie, if the cell begins with "PM-" then colour the cell and colour the text. *I'd be grateful for any suggestions, thanks. Private Sub Worksheet_Change(ByVal Target As Range) Dim Num As Long Dim rng As Range Dim vRngInput As Range * * Set vRngInput = Intersect(Target, Range("A1:iv63000")) * * If vRngInput Is Nothing Then Exit Sub * * * * On Error GoTo endit * * Application.EnableEvents = False * * For Each rng In vRngInput * * 'Determine the color * * * * Select Case UCase(rng.Value) * * * * Case Is = "PM": Num = 22 * * * * Case Is = "BH": Num = 45 * * * * Case Is = "H": Num = 4 * * * * Case Is = "PE": Num = 35 * * * * Case Is = "E": Num = 5 * * * * Case Is = "R": Num = 6 * * * * Case Is = "P": Num = 29 * * * * Case Is = "A": Num = 7 * * * * Case Is = "PRE": Num = 8 * * * * End Select * * 'Apply the color * * rng.Interior.ColorIndex = Num * * Next rng endit: * * * * Application.EnableEvents = True End Sub -- Marie Bayes |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Conditional Format VB Code for Begins With
Thanks Peter, I'm afraid I need a little more help than that, ie, I'm
applying this code to most of the spreadsheet, so what do I put in the 'text' part of the LEN code, plus how do I then format the text colour in each cell? -- Marie Bayes "Pete_UK" wrote: You'd normally use: Left( text , no_of_characters ) to check "begins with" but in your case you have 1-, 2- and 3- character strings, so this would be more complicated. Pete On Dec 19, 2:38 pm, Marie Bayes wrote: Hi I've cribbed the following code from this site to use for some conditional formatting. However, i'd like to change it so that if the cells BEGIN WITH the letters I've put into the code the formatting will work. Plus I'd also like to format the colour of the text using the same critieria, ie, if the cell begins with "PM-" then colour the cell and colour the text. I'd be grateful for any suggestions, thanks. Private Sub Worksheet_Change(ByVal Target As Range) Dim Num As Long Dim rng As Range Dim vRngInput As Range Set vRngInput = Intersect(Target, Range("A1:iv63000")) If vRngInput Is Nothing Then Exit Sub On Error GoTo endit Application.EnableEvents = False For Each rng In vRngInput 'Determine the color Select Case UCase(rng.Value) Case Is = "PM": Num = 22 Case Is = "BH": Num = 45 Case Is = "H": Num = 4 Case Is = "PE": Num = 35 Case Is = "E": Num = 5 Case Is = "R": Num = 6 Case Is = "P": Num = 29 Case Is = "A": Num = 7 Case Is = "PRE": Num = 8 End Select 'Apply the color rng.Interior.ColorIndex = Num Next rng endit: Application.EnableEvents = True End Sub -- Marie Bayes |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Conditional Format VB Code for Begins With
Replace this part of your code:
Select Case UCase(rng.Value) Case Is = "PM": Num = 22 Case Is = "BH": Num = 45 Case Is = "H": Num = 4 Case Is = "PE": Num = 35 Case Is = "E": Num = 5 Case Is = "R": Num = 6 Case Is = "P": Num = 29 Case Is = "A": Num = 7 Case Is = "PRE": Num = 8 End Select with this: IF(Left(UCase(rng.Value),2) = "PH" Then Num = 22 IF(Left(UCase(rng.Value),2) = "BH" Then Num = 45 IF(Left(UCase(rng.Value),1) = "H" Then Num = 4 IF(Left(UCase(rng.Value),2) = "PE" Then Num = 35 IF(Left(UCase(rng.Value),1) = "E" Then Num = 5 IF(Left(UCase(rng.Value),1) = "R" Then Num = 6 IF(Left(UCase(rng.Value),1) = "P" Then Num = 29 IF(Left(UCase(rng.Value),1) = "A" Then Num = 7 IF(Left(UCase(rng.Value),3) = "PRE" Then Num = 8 So that you do not need the Select Case or End Select statements or anything in between. Hope this helps. Pete On Dec 19, 4:04*pm, Marie Bayes wrote: Thanks Peter, I'm afraid I need a little more help than that, ie, I'm applying this code to most of the spreadsheet, so what do I put in the 'text' part of the LEN code, plus how do I then format the text colour in each cell? -- Marie Bayes "Pete_UK" wrote: You'd normally use: Left( text , no_of_characters ) to check "begins with" but in your case you have 1-, 2- and 3- character strings, so this would be more complicated. Pete On Dec 19, 2:38 pm, Marie Bayes wrote: Hi I've cribbed the following code from this site to use for some conditional formatting. *However, i'd like to change it so that if the cells BEGIN WITH * the letters I've put into the code the formatting will work. *Plus I'd also like to format the colour of the text using the same critieria, ie, if the cell begins with "PM-" then colour the cell and colour the text. *I'd be grateful for any suggestions, thanks. Private Sub Worksheet_Change(ByVal Target As Range) Dim Num As Long Dim rng As Range Dim vRngInput As Range * * Set vRngInput = Intersect(Target, Range("A1:iv63000")) * * If vRngInput Is Nothing Then Exit Sub * * * * On Error GoTo endit * * Application.EnableEvents = False * * For Each rng In vRngInput * * 'Determine the color * * * * Select Case UCase(rng.Value) * * * * Case Is = "PM": Num = 22 * * * * Case Is = "BH": Num = 45 * * * * Case Is = "H": Num = 4 * * * * Case Is = "PE": Num = 35 * * * * Case Is = "E": Num = 5 * * * * Case Is = "R": Num = 6 * * * * Case Is = "P": Num = 29 * * * * Case Is = "A": Num = 7 * * * * Case Is = "PRE": Num = 8 * * * * End Select * * 'Apply the color * * rng.Interior.ColorIndex = Num * * Next rng endit: * * * * Application.EnableEvents = True End Sub -- Marie Bayes- Hide quoted text - - Show quoted text - |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Conditional Format VB Code for Begins With
That's great Pete, worked a treat (once I'd added a ')' that was missed off
each line). Do you know the code to change the font colour? -- Marie Bayes "Pete_UK" wrote: Replace this part of your code: Select Case UCase(rng.Value) Case Is = "PM": Num = 22 Case Is = "BH": Num = 45 Case Is = "H": Num = 4 Case Is = "PE": Num = 35 Case Is = "E": Num = 5 Case Is = "R": Num = 6 Case Is = "P": Num = 29 Case Is = "A": Num = 7 Case Is = "PRE": Num = 8 End Select with this: IF(Left(UCase(rng.Value),2) = "PH" Then Num = 22 IF(Left(UCase(rng.Value),2) = "BH" Then Num = 45 IF(Left(UCase(rng.Value),1) = "H" Then Num = 4 IF(Left(UCase(rng.Value),2) = "PE" Then Num = 35 IF(Left(UCase(rng.Value),1) = "E" Then Num = 5 IF(Left(UCase(rng.Value),1) = "R" Then Num = 6 IF(Left(UCase(rng.Value),1) = "P" Then Num = 29 IF(Left(UCase(rng.Value),1) = "A" Then Num = 7 IF(Left(UCase(rng.Value),3) = "PRE" Then Num = 8 So that you do not need the Select Case or End Select statements or anything in between. Hope this helps. Pete On Dec 19, 4:04 pm, Marie Bayes wrote: Thanks Peter, I'm afraid I need a little more help than that, ie, I'm applying this code to most of the spreadsheet, so what do I put in the 'text' part of the LEN code, plus how do I then format the text colour in each cell? -- Marie Bayes "Pete_UK" wrote: You'd normally use: Left( text , no_of_characters ) to check "begins with" but in your case you have 1-, 2- and 3- character strings, so this would be more complicated. Pete On Dec 19, 2:38 pm, Marie Bayes wrote: Hi I've cribbed the following code from this site to use for some conditional formatting. However, i'd like to change it so that if the cells BEGIN WITH the letters I've put into the code the formatting will work. Plus I'd also like to format the colour of the text using the same critieria, ie, if the cell begins with "PM-" then colour the cell and colour the text. I'd be grateful for any suggestions, thanks. Private Sub Worksheet_Change(ByVal Target As Range) Dim Num As Long Dim rng As Range Dim vRngInput As Range Set vRngInput = Intersect(Target, Range("A1:iv63000")) If vRngInput Is Nothing Then Exit Sub On Error GoTo endit Application.EnableEvents = False For Each rng In vRngInput 'Determine the color Select Case UCase(rng.Value) Case Is = "PM": Num = 22 Case Is = "BH": Num = 45 Case Is = "H": Num = 4 Case Is = "PE": Num = 35 Case Is = "E": Num = 5 Case Is = "R": Num = 6 Case Is = "P": Num = 29 Case Is = "A": Num = 7 Case Is = "PRE": Num = 8 End Select 'Apply the color rng.Interior.ColorIndex = Num Next rng endit: Application.EnableEvents = True End Sub -- Marie Bayes- Hide quoted text - - Show quoted text - |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Conditional Format VB Code for Begins With
Sorry about the missing bracket - I realised after I had posted.
At the bottom of your code you have: rng.Interior.ColorIndex = Num which sets the background colour. You will need to add this statement below it: rng.Font.ColorIndex = Num2 to set the foreground colour. However, you will need to define what Num2 is, so at the top of your code add this line: Dim Num2 As Long and then for each of those statements that I gave you you will have to specify what value you want for Num2, like this: IF(Left(UCase(rng.Value),2) = "PH") Then Num = 22 : Num2 = 4 for the first one. (Be wary of word wrap when you view this post - this should be all one line). You will need to specify an appropriate colour for Num2 for each of those if statements. Hope this helps. Pete On Dec 19, 4:50*pm, Marie Bayes wrote: That's great Pete, worked a treat (once I'd added a ')' that was missed off each line). *Do you know the code to change the font colour? -- Marie Bayes "Pete_UK" wrote: Replace this part of your code: * * * * Select Case UCase(rng.Value) * * * * Case Is = "PM": Num = 22 * * * * Case Is = "BH": Num = 45 * * * * Case Is = "H": Num = 4 * * * * Case Is = "PE": Num = 35 * * * * Case Is = "E": Num = 5 * * * * Case Is = "R": Num = 6 * * * * Case Is = "P": Num = 29 * * * * Case Is = "A": Num = 7 * * * * Case Is = "PRE": Num = 8 * * * * End Select with this: * * * * IF(Left(UCase(rng.Value),2) = "PH" Then Num = 22 * * * * IF(Left(UCase(rng.Value),2) = "BH" Then Num = 45 * * * * IF(Left(UCase(rng.Value),1) = "H" Then Num = 4 * * * * IF(Left(UCase(rng.Value),2) = "PE" Then Num = 35 * * * * IF(Left(UCase(rng.Value),1) = "E" Then Num = 5 * * * * IF(Left(UCase(rng.Value),1) = "R" Then Num = 6 * * * * IF(Left(UCase(rng.Value),1) = "P" Then Num = 29 * * * * IF(Left(UCase(rng.Value),1) = "A" Then Num = 7 * * * * IF(Left(UCase(rng.Value),3) = "PRE" Then Num = 8 So that you do not need the Select Case or End Select statements or anything in between. Hope this helps. Pete On Dec 19, 4:04 pm, Marie Bayes wrote: Thanks Peter, I'm afraid I need a little more help than that, ie, I'm applying this code to most of the spreadsheet, so what do I put in the 'text' part of the LEN code, plus how do I then format the text colour in each cell? -- Marie Bayes "Pete_UK" wrote: You'd normally use: Left( text , no_of_characters ) to check "begins with" but in your case you have 1-, 2- and 3- character strings, so this would be more complicated. Pete On Dec 19, 2:38 pm, Marie Bayes wrote: Hi I've cribbed the following code from this site to use for some conditional formatting. *However, i'd like to change it so that if the cells BEGIN WITH * the letters I've put into the code the formatting will work. *Plus I'd also like to format the colour of the text using the same critieria, ie, if the cell begins with "PM-" then colour the cell and colour the text. *I'd be grateful for any suggestions, thanks. Private Sub Worksheet_Change(ByVal Target As Range) Dim Num As Long Dim rng As Range Dim vRngInput As Range * * Set vRngInput = Intersect(Target, Range("A1:iv63000")) * * If vRngInput Is Nothing Then Exit Sub * * * * On Error GoTo endit * * Application.EnableEvents = False * * For Each rng In vRngInput * * 'Determine the color * * * * Select Case UCase(rng.Value) * * * * Case Is = "PM": Num = 22 * * * * Case Is = "BH": Num = 45 * * * * Case Is = "H": Num = 4 * * * * Case Is = "PE": Num = 35 * * * * Case Is = "E": Num = 5 * * * * Case Is = "R": Num = 6 * * * * Case Is = "P": Num = 29 * * * * Case Is = "A": Num = 7 * * * * Case Is = "PRE": Num = 8 * * * * End Select * * 'Apply the color * * rng.Interior.ColorIndex = Num * * Next rng endit: * * * * Application.EnableEvents = True End Sub -- Marie Bayes- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| If statement - begins with | Excel Discussion (Misc queries) | |||
| New Conditional Format Overriding Previous Conditional Format | Excel Discussion (Misc queries) | |||
| Code Date Format Depending on Computer format | Excel Discussion (Misc queries) | |||
| IF Function - Begins With | Excel Discussion (Misc queries) | |||
| Code for Conditional format | Excel Discussion (Misc queries) | |||
Linear Mode
