QuoteOzgrid was once the busiest excel forum on the net, unfortunately there were a number of factors that led to its decline in numbers
Maybe things like this led to it.
QuoteOh stop being such a drama queen Ali, its getting tiresome.
Posts by skywriter
-
-
Yes, people come primarily for the content, but a badly designed interface does put some off (I have seen this happen on other forums, so I know it can influence the membership to some extent). I hope that the current interface here, which is badly deigned (my opinion - your mileage may vary), will be tweaked based on the feedback offered so far.
I agree.
I'm hopeful also, but there seems to be too much of the sorry this version doesn't do that or it requires coding that may not stick with upgrades or updates or whatever. -
My pleasure, thanks for the feedback. :cheers:
I just noticed there's an error in the ElseIf line, if you read this just change it to look like the other line.
In other words change the C10 part to match the other line that looks at what is in C10, D10 etc.CodeCells(10, c).Value -
I think this is what you are asking for. :cheers:
Code
Display MorePrivate Sub Worksheet_Change(ByVal Target As Range) 'Color cells based on Selection If Target.Count > 1 Then Exit Sub If Intersect(Range("C10:G10"), Target) Is Nothing Then Exit Sub Application.EnableEvents = False Dim c As Long c = Target.Column If Cells(10, c).Value = "COVERAGE" Then 'N/A Range(Cells(19, c), Cells(23, c)).Interior.ColorIndex = xlColorIndexNone 'Highlight Cells Range(Cells(19, c), Cells(23, c)).Interior.ColorIndex = 15 'Highlight cells Range(Cells(19, c), Cells(23, c)).ClearContents ' Clears Item Range(Cells(40, c), Cells(45, c)).Interior.ColorIndex = xlColorIndexNone 'Highlight Cells Range(Cells(40, c), Cells(45, c)).Interior.ColorIndex = 15 'Highlight cells Range(Cells(40, c), Cells(45, c)).ClearContents ' Clears Item ElseIf Range("C10").Value = "CONSUMABLE" Then 'Consumable Range(Cells(19, c), Cells(23, c)).Interior.ColorIndex = xlColorIndexNone 'Highlight Cells Range(Cells(40, c), Cells(45, c)).Interior.ColorIndex = xlColorIndexNone 'Highlight Cells Range(Cells(20, c), Cells(23, c)).Interior.ColorIndex = 15 'Highlight cells Range(Cells(20, c), Cells(23, c)).ClearContents ' Clears Item Range(Cells(40, c), Cells(45, c)).Interior.ColorIndex = 15 'Highlight cells Range(Cells(40, c), Cells(45, c)).ClearContents ' Clears Item End If Application.EnableEvents = True End Sub -
You can try this, it's untested as I don't have a sheet to test it with and I'm too lazy to create one. :cheers:
Code
Display MoreOption Explicit Private Sub Worksheet_Change(ByVal Target As Range) 'Color cells based on Selection If Target.Address = "$C$10" Then Application.EnableEvents = False Dim c As Long For c = 3 To 7 If Cells(10, c).Value = "COVERAGE" Then 'N/A Range(Cells(19, c), Cells(23, c)).Interior.ColorIndex = xlColorIndexNone 'Highlight Cells Range(Cells(19, c), Cells(23, c)).Interior.ColorIndex = 15 'Highlight cells Range(Cells(19, c), Cells(23, c)).ClearContents ' Clears Item Range(Cells(40, c), Cells(45, c)).Interior.ColorIndex = xlColorIndexNone 'Highlight Cells Range(Cells(40, c), Cells(45, c)).Interior.ColorIndex = 15 'Highlight cells Range(Cells(40, c), Cells(45, c)).ClearContents ' Clears Item ElseIf Range("C10").Value = "CONSUMABLE" Then 'Consumable Range(Cells(19, c), Cells(23, c)).Interior.ColorIndex = xlColorIndexNone 'Highlight Cells Range(Cells(40, c), Cells(45, c)).Interior.ColorIndex = xlColorIndexNone 'Highlight Cells Range(Cells(20, c), Cells(23, c)).Interior.ColorIndex = 15 'Highlight cells Range(Cells(20, c), Cells(23, c)).ClearContents ' Clears Item Range(Cells(40, c), Cells(45, c)).Interior.ColorIndex = 15 'Highlight cells Range(Cells(40, c), Cells(45, c)).ClearContents ' Clears Item End If Next c Application.EnableEvents = True End If End Sub -
My pleasure, thanks for the feedback. :cheers:
-
Try:
CodeMe.TxtRef.Value = findvalue(1, 2) -
My pleasure, thanks for the feedback. :cheers:
QuoteI don't see anyway to leave a positive feedback/ranking, if it's there someone please let me know.
It's the like button below.
-
I ran it against your sample and it worked fine.
If your sample doesn't accurately reflect your real spreadsheet then you would need to upload a more accurate sample.
-
luuminhvuong93 welcome to the board,
Please review the rules you agreed to when you joined this site.
You posted a question in a thread started by someone else, this is referred to as thread hijacking.
I have closed the thread, feel free to start your own thread(its' free) and link to this thread if you so desire. -
Maybe something like this.
Code
Display MoreSub Pearl() Dim r As Range ' Pearl Harbor Macro Application.ScreenUpdating = False Sheets("Location").Select ActiveSheet.ListObjects("Table_Custom_Queries.accdb3").Range.AutoFilter Field _ :=5, Criteria1:="HI, PEARL HARBOR" For Each r In Intersect(ActiveSheet.ListObjects("Table_Custom_Queries.accdb3").DataBodyRange.SpecialCells(12), _ ActiveSheet.ListObjects("Table_Custom_Queries.accdb3").ListColumns(26).DataBodyRange) r.Value = "found" Next r Application.ScreenUpdating = True End Sub -
-
I sent you a message, please check your messages.
This is a double post.
This thread will be closed, you may continue in the duplicate thread.
-
wilsti6 welcome to the board.
I'm not sure why you triple posted the same subject.
Please take a moment to read the rules you agreed to when you joined this board.
You should be able to edit your post if you make a mistake, or if you have to add to your original post do so in a separate post below the original, do not create new threads.
If you have issues send a PM to a moderator and we'll be happy to assist you in any way we can.Thanks for your cooperation. :cheers:
-
-
My pleasure, thanks for the feedback. :cheers:
-
-
Maybe this formula in L8, you must use control + shift + enter to confirm the formula as it is an array formula and then you can copy it normally to the other cells.
=INDEX($L$17:$L$25,MATCH($J8&$K8,$J$17:$J$25&$K$17:$K$25,1))
-
-