| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formatting 3 Conditions
Hello Again!
Can anyone help me at all, please? Having used this discussion group to get some terrific results using VBA I have now encountered a problem. I have been using a script (sample below) to get past the 3 conditions problem with conditional formatting and have also been able to get the cells into which data is entered to adopt the same colour as various pairs of references in a range where upper and lower limit parameter values are held (hope that makes sense). I have had cause to add some additional rows of data beyond those which I originally defined, and as such have simply amended the VBA code so that the Target Range includes some additional cell addresses. However, when I enter data into cells in these additional rows (57 & 58) they do not change colour. In addition, when I delete the value from the cell I get a Run Time Error '13': message - Type Mismatch. When select 'Debug' it always goes to the same place in the script where the error supposedly occurs, however I can't see anything wrong, or at least I can't see anything different in the script from how it was when it worked. Finally, the problem doesn't seem confined to the rows I have added in, as changing values in any of the cells previously coloured successfully by the script gives me the same errors, i.e. it doesn't colour the cell and deleting the value gives me a Run Time Error. What have I done wrong? Can anyone help at all, please? I am so exasperated, this script seemed like the answer to my prayers but has stopped working and I am at a loss to know how to correct it. Any help gratefully received. Thanks, Dickie NB - This is a selection from the script, it simply repeats itself for each pair of columns, with the error always appearing to occur in the first instance of the pairs of Case Ranges (in this instance F2 to G2). Dim iColor As Integer If Not Intersect(Target, Range("F19:G100")) Is Nothing Then Select Case Target Case Range("F2").Value To Range("G2").Value iColor = Cells(2, 4).Interior.ColorIndex Case Range("F3").Value To Range("G3").Value iColor = Cells(3, 4).Interior.ColorIndex Case Range("F4").Value To Range("G4").Value iColor = Cells(4, 4).Interior.ColorIndex Case Range("F5").Value To Range("G5").Value iColor = Cells(5, 4).Interior.ColorIndex Case Range("F6").Value To Range("G6").Value iColor = Cells(6, 4).Interior.ColorIndex Case Range("F7").Value To Range("G7").Value iColor = Cells(7, 4).Interior.ColorIndex Case Range("F8").Value To Range("G8").Value iColor = Cells(8, 4).Interior.ColorIndex Case Range("F9").Value To Range("G9").Value iColor = Cells(9, 4).Interior.ColorIndex Case Range("F10").Value To Range("G10").Value iColor = Cells(10, 4).Interior.ColorIndex Case Else End Select Target.Interior.ColorIndex = iColor End If |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Conditional Formatting 3 Conditions
Dickie,
Try the one liner: icolor = Cells(Application.WorksheetFunction.Match(Target.V alue, Range("F:F")), 4).Interior.ColorIndex HTH, Bernie MS Excel MVP "Dickie Worton" wrote in message ... Hello Again! Can anyone help me at all, please? Having used this discussion group to get some terrific results using VBA I have now encountered a problem. I have been using a script (sample below) to get past the 3 conditions problem with conditional formatting and have also been able to get the cells into which data is entered to adopt the same colour as various pairs of references in a range where upper and lower limit parameter values are held (hope that makes sense). I have had cause to add some additional rows of data beyond those which I originally defined, and as such have simply amended the VBA code so that the Target Range includes some additional cell addresses. However, when I enter data into cells in these additional rows (57 & 58) they do not change colour. In addition, when I delete the value from the cell I get a Run Time Error '13': message - Type Mismatch. When select 'Debug' it always goes to the same place in the script where the error supposedly occurs, however I can't see anything wrong, or at least I can't see anything different in the script from how it was when it worked. Finally, the problem doesn't seem confined to the rows I have added in, as changing values in any of the cells previously coloured successfully by the script gives me the same errors, i.e. it doesn't colour the cell and deleting the value gives me a Run Time Error. What have I done wrong? Can anyone help at all, please? I am so exasperated, this script seemed like the answer to my prayers but has stopped working and I am at a loss to know how to correct it. Any help gratefully received. Thanks, Dickie NB - This is a selection from the script, it simply repeats itself for each pair of columns, with the error always appearing to occur in the first instance of the pairs of Case Ranges (in this instance F2 to G2). Dim iColor As Integer If Not Intersect(Target, Range("F19:G100")) Is Nothing Then Select Case Target Case Range("F2").Value To Range("G2").Value iColor = Cells(2, 4).Interior.ColorIndex Case Range("F3").Value To Range("G3").Value iColor = Cells(3, 4).Interior.ColorIndex Case Range("F4").Value To Range("G4").Value iColor = Cells(4, 4).Interior.ColorIndex Case Range("F5").Value To Range("G5").Value iColor = Cells(5, 4).Interior.ColorIndex Case Range("F6").Value To Range("G6").Value iColor = Cells(6, 4).Interior.ColorIndex Case Range("F7").Value To Range("G7").Value iColor = Cells(7, 4).Interior.ColorIndex Case Range("F8").Value To Range("G8").Value iColor = Cells(8, 4).Interior.ColorIndex Case Range("F9").Value To Range("G9").Value iColor = Cells(9, 4).Interior.ColorIndex Case Range("F10").Value To Range("G10").Value iColor = Cells(10, 4).Interior.ColorIndex Case Else End Select Target.Interior.ColorIndex = iColor End If |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Conditional Formatting 3 Conditions
Hi Bernie,
Thanks for responding, sorry for the delay in trying this out but I think time differences have been a factor! OK, I'm sorry but I need to ask whereabouts I should insert your suggested one-liner into my existing script, I'm still pretty new to VBA programming and need this spelled out pretty simply if I'm not to mess up. Regards, Dickie "Bernie Deitrick" wrote: Dickie, Try the one liner: icolor = Cells(Application.WorksheetFunction.Match(Target.V alue, Range("F:F")), 4).Interior.ColorIndex HTH, Bernie MS Excel MVP "Dickie Worton" wrote in message ... Hello Again! Can anyone help me at all, please? Having used this discussion group to get some terrific results using VBA I have now encountered a problem. I have been using a script (sample below) to get past the 3 conditions problem with conditional formatting and have also been able to get the cells into which data is entered to adopt the same colour as various pairs of references in a range where upper and lower limit parameter values are held (hope that makes sense). I have had cause to add some additional rows of data beyond those which I originally defined, and as such have simply amended the VBA code so that the Target Range includes some additional cell addresses. However, when I enter data into cells in these additional rows (57 & 58) they do not change colour. In addition, when I delete the value from the cell I get a Run Time Error '13': message - Type Mismatch. When select 'Debug' it always goes to the same place in the script where the error supposedly occurs, however I can't see anything wrong, or at least I can't see anything different in the script from how it was when it worked. Finally, the problem doesn't seem confined to the rows I have added in, as changing values in any of the cells previously coloured successfully by the script gives me the same errors, i.e. it doesn't colour the cell and deleting the value gives me a Run Time Error. What have I done wrong? Can anyone help at all, please? I am so exasperated, this script seemed like the answer to my prayers but has stopped working and I am at a loss to know how to correct it. Any help gratefully received. Thanks, Dickie NB - This is a selection from the script, it simply repeats itself for each pair of columns, with the error always appearing to occur in the first instance of the pairs of Case Ranges (in this instance F2 to G2). Dim iColor As Integer If Not Intersect(Target, Range("F19:G100")) Is Nothing Then Select Case Target Case Range("F2").Value To Range("G2").Value iColor = Cells(2, 4).Interior.ColorIndex Case Range("F3").Value To Range("G3").Value iColor = Cells(3, 4).Interior.ColorIndex Case Range("F4").Value To Range("G4").Value iColor = Cells(4, 4).Interior.ColorIndex Case Range("F5").Value To Range("G5").Value iColor = Cells(5, 4).Interior.ColorIndex Case Range("F6").Value To Range("G6").Value iColor = Cells(6, 4).Interior.ColorIndex Case Range("F7").Value To Range("G7").Value iColor = Cells(7, 4).Interior.ColorIndex Case Range("F8").Value To Range("G8").Value iColor = Cells(8, 4).Interior.ColorIndex Case Range("F9").Value To Range("G9").Value iColor = Cells(9, 4).Interior.ColorIndex Case Range("F10").Value To Range("G10").Value iColor = Cells(10, 4).Interior.ColorIndex Case Else End Select Target.Interior.ColorIndex = iColor End If |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Conditional Formatting 3 Conditions
Dickie,
Comment out the entire select case block of code (And here's a VBE tip, in case you don't know how to comment out lots of code quickly (since you're new): in the VBE, select View, then Toolbars, and make sure that Edit is checked. Then select the block of code, and click the "Comment Block" button. You can hover your mouse cursor over each of the buttons to learn what it is called... And, of course, there is an "Uncomment Block" button, too.) Comment out from Select Case Target to End Select and put the line of code that I suggested just before you set the colorindex: icolor = Cells(Application.WorksheetFunction.Match( _ Target.Value, Range("F:F")), 4).Interior.ColorIndex Target.Interior.ColorIndex = iColor Of course, if it works, you could combine these into one line: Target.Interior.ColorIndex = Cells(Application.WorksheetFunction.Match( _ Target.Value, Range("F:F")), 4).Interior.ColorIndex HTH, Bernie MS Excel MVP "Dickie Worton" wrote in message ... Hi Bernie, Thanks for responding, sorry for the delay in trying this out but I think time differences have been a factor! OK, I'm sorry but I need to ask whereabouts I should insert your suggested one-liner into my existing script, I'm still pretty new to VBA programming and need this spelled out pretty simply if I'm not to mess up. Regards, Dickie "Bernie Deitrick" wrote: Dickie, Try the one liner: icolor = Cells(Application.WorksheetFunction.Match(Target.V alue, Range("F:F")), 4).Interior.ColorIndex HTH, Bernie MS Excel MVP "Dickie Worton" wrote in message ... Hello Again! Can anyone help me at all, please? Having used this discussion group to get some terrific results using VBA I have now encountered a problem. I have been using a script (sample below) to get past the 3 conditions problem with conditional formatting and have also been able to get the cells into which data is entered to adopt the same colour as various pairs of references in a range where upper and lower limit parameter values are held (hope that makes sense). I have had cause to add some additional rows of data beyond those which I originally defined, and as such have simply amended the VBA code so that the Target Range includes some additional cell addresses. However, when I enter data into cells in these additional rows (57 & 58) they do not change colour. In addition, when I delete the value from the cell I get a Run Time Error '13': message - Type Mismatch. When select 'Debug' it always goes to the same place in the script where the error supposedly occurs, however I can't see anything wrong, or at least I can't see anything different in the script from how it was when it worked. Finally, the problem doesn't seem confined to the rows I have added in, as changing values in any of the cells previously coloured successfully by the script gives me the same errors, i.e. it doesn't colour the cell and deleting the value gives me a Run Time Error. What have I done wrong? Can anyone help at all, please? I am so exasperated, this script seemed like the answer to my prayers but has stopped working and I am at a loss to know how to correct it. Any help gratefully received. Thanks, Dickie NB - This is a selection from the script, it simply repeats itself for each pair of columns, with the error always appearing to occur in the first instance of the pairs of Case Ranges (in this instance F2 to G2). Dim iColor As Integer If Not Intersect(Target, Range("F19:G100")) Is Nothing Then Select Case Target Case Range("F2").Value To Range("G2").Value iColor = Cells(2, 4).Interior.ColorIndex Case Range("F3").Value To Range("G3").Value iColor = Cells(3, 4).Interior.ColorIndex Case Range("F4").Value To Range("G4").Value iColor = Cells(4, 4).Interior.ColorIndex Case Range("F5").Value To Range("G5").Value iColor = Cells(5, 4).Interior.ColorIndex Case Range("F6").Value To Range("G6").Value iColor = Cells(6, 4).Interior.ColorIndex Case Range("F7").Value To Range("G7").Value iColor = Cells(7, 4).Interior.ColorIndex Case Range("F8").Value To Range("G8").Value iColor = Cells(8, 4).Interior.ColorIndex Case Range("F9").Value To Range("G9").Value iColor = Cells(9, 4).Interior.ColorIndex Case Range("F10").Value To Range("G10").Value iColor = Cells(10, 4).Interior.ColorIndex Case Else End Select Target.Interior.ColorIndex = iColor End If |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Conditional formatting - with 2 conditions | Excel Discussion (Misc queries) | |||
| VBA & Conditional Formatting 3 conditions | Excel Programming | |||
| Conditional Formatting for more than 3 conditions | Excel Worksheet Functions | |||
| VB for conditional formatting more than 3 conditions | Excel Programming | |||
| more than 3 conditions in conditional formatting - possible? | Excel Discussion (Misc queries) | |||
Linear Mode
