Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Coding Using VLookup
Hi,
I'm trying to color in a cell based on a vlookup. Can anyone tell me how to do this? The VBA coding seems to work fine, if I type in the color name but I can't type in the name, I need the vlookup to populate the cell with the color name and then I need the VBA coding to color the cell based on that color name. Any help would be appreciated. Thank you. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Coding Using VLookup
Antney,
Have you tried Conditional Formatting from the Format menu? In the Conditional Formatting, you can say if red, color the cell with red. I hope that helps. "Antney" wrote: Hi, I'm trying to color in a cell based on a vlookup. Can anyone tell me how to do this? The VBA coding seems to work fine, if I type in the color name but I can't type in the name, I need the vlookup to populate the cell with the color name and then I need the VBA coding to color the cell based on that color name. Any help would be appreciated. Thank you. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Coding Using VLookup
yuo don't give much information, but lets suppose that you have a table,
range named colorTable where the color name is in the first column and the excel color is in column 2- the cell in 2 is colored not named.this gives you flexibility I'll also assume that your target cell is data validated using the list from the first column of colorTable, the column I have named 'colors' ....this is used in my check... to achieve your goal, we'll use the CHANGE event fired when th evalue of the target cell changes. You will need to have some "rule" such as this will affect cells in a certain range or column. My code is this illustrative .... opne the sheet's code page by right-clicking the tab and selecting view code from the pop-up menu, and paste this code Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo quit If Target.Validation.Formula1 = "=colors" Then SetColor Target End If quit: End Sub Sub SetColor(Target As Range) Dim index As Long On Error Resume Next 'trap if there's no validation index = WorksheetFunction.Match(Target.Value, Range("colors"), False) If index 0 Then ' trap if there's no match Target.Interior.Color = Range("tableColor").Resize(1, 1).Cells(index, 2).Interior.Color End If On Error GoTo 0 End Sub "Antney" wrote: Hi, I'm trying to color in a cell based on a vlookup. Can anyone tell me how to do this? The VBA coding seems to work fine, if I type in the color name but I can't type in the name, I need the vlookup to populate the cell with the color name and then I need the VBA coding to color the cell based on that color name. Any help would be appreciated. Thank you. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VBA coding a nested Vlookup, and a sumif formula | Excel Programming | |||
Please help on coding | Excel Programming | |||
"=ROW()-1" type of coding doesn't appear in a filter / is there coding that does? | Excel Programming | |||
Implant macro coding into ASP coding | Excel Programming | |||
Coding in VLOOKUP function | Excel Programming |