Hello people
Usually the search function works great and after some time it gets me what im looking for but not this time im afraid, altho it seems simple (i guess)
I have a worksheet that has product names in columns and lot numbers in rows. Now i am looking for a vba code piece that simply looks at the complete product name (eg the name "LP-NV" in D4) and if the cell also meets the condition that a lot number starts with the letter "D" in column B the cell should color gray or yellow, color isnt that important as long as it shows up.
FK1 untill FL26 shows the relation between lotnr and product names (LP-NV lot starts with D, Pro-V lot starts with P etc)
So if lot starts in a row at column B with D*** the cells D6 E6 F6 should colour, else stay white. Same goes for lotnumber starting with J*** matches the LP-NNW column etc
Once a colored cell gains data (numbers) the conditional formatted color should remain there
I marked the column matches myself with the text 'Color' to give an idea what im trying to do. I dont know if every product columns need a new piece of code? But if i have a start i can try to adjust the rest myself, altho i consider my vba knowledge as 'starter'
There are probably a dozen ways to do this but please try to keep the code erghm simple
The reason im looking for VBA is because it is flexible and i can use it for different sheets because i have 8 other sheets to add these CF's to.
Ohhh before i forget: i already have some vba piece in the sheet so im not sure if there can be more added to the part that starts with "Private Sub Worksheet_Change(ByVal Target As Excel.Range)" ?
Thanks for the help in advance people!! Here is the example file added.
************* not sure if i need to make a new post for this? ******************
EDIT: while im posting and searching for another issue: is it possible to round off numbers when importing them from another sheet?
Code below imports from a preselected workbook but it imports the complete number (for example 0.351256585 while i only need 3 decimals: 0.351). I think something needs to be added after the line " .Range("D10").Formula = wb.Worksheets("Whiteness").Range("$E$5").Value" but im not sure what ><
Im using this code to make the importing work (yes the code gives the error "do you want to save changes to ...." but im still working on that lol):
[VBA]Sub GetDataFromClosedWorkbook()
Dim wb As Workbook
Application.ScreenUpdating = False ' turn off the screen updating
Set wb = Workbooks.Open(Filename:=Range("BF2").Value) ' Set wb = Workbooks
' open the source workbook, read only
ActiveWorkbook.Saved = True
Cancel = True
SaveAsUI = False
With ThisWorkbook.Worksheets("Dump")
On Error Resume Next
Sheets("Dump").Range("D10
' read data from the source workbook
.Range("D10").Formula = wb.Worksheets("Whiteness").Range("$E$5").Value 'WHT Ref
.Range("D11").Formula = wb.Worksheets("Report data").Range("$P$5").Value 'WHT
.Range("D12").Formula = wb.Worksheets("Report data").Range("$U$5").Value 'SR
.Range("D13").Formula = wb.Worksheets("Sensitivity").Range("$T$5").Value 'SS
.Range("D14").Formula = wb.Worksheets("XRF MC+OC amount").Range("$E$5").Value 'MC (XRF ref)
.Range("D15").Formula = wb.Worksheets("XRF MC+OC amount").Range("$R$5").Value 'OC (XRF ref)
.Range("D16").Formula = wb.Worksheets("Elements").Range("$T$5").Value 'Elementen P
.Range("D17").Formula = wb.Worksheets("Elements").Range("$P$5").Value 'Elementen Ca
.Range("D18").Formula = wb.Worksheets("Elements").Range("$E$5").Value 'Elementen Si
.Range("D19").Formula = wb.Worksheets("Elements").Range("$I$5").Value 'Elementen AD F
.Range("D20").Formula = wb.Worksheets("Elements").Range("$I$5").Value 'Elementen AD B
.Range("D21").Formula = wb.Worksheets("Blue Residu").Range("$E$5").Value 'Elementen BR
End With
wb.Close False ' close the source workbook without saving any changes
Set wb = Nothing ' free memory
Application.ScreenUpdating = True ' turn on the screen updating
End Sub[/VBA]
Reply With Quote
Originally Posted by CatDaddy
)
