Vlookup a cell format from another cell

[djharish619]

Hi friends,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
I am trying to create a report in which I have to update the value of the cells based on the result on some table.<o></o>
<o></o>
The table consists of 3 columns, namely category, current yr, previous yr. The results for each category and year are color coded e.g. green, yellow and grey. And note that the value of the cell in the table is formatted using conditional formatting.<o></o>
<o></o>
I would like to look up all cell value (say B5) in a workbook or selection; into the table -> category<o></o>
And update cell B5 color with current year result and cell C5 color with previous color. I am not sure how should I proceed, could you please help.

<o>

</o>

 

[tweedle]

I believe you will need to apply the same conditional formats on your table to your vlookup formula.

 

[djharish619]

Thanks for your reply...

However the table you can see in the image is the table from where I have to lookup formats into another workbook. It is created with conditional formating. I manually update the table using the conditional formats and would like to see the same effect on another sheet.

The another sheet (where I have to look up these color formats) have the same categories with adjacent cells as current and previous (same as in table); but all the categories are not in the same row/column they are spread all over the sheet.

Also I cannot use conditional formatting because it helps me add only 3 conditions, whereas I have more than 3 conditions (say 10). If I use conditional formating I have to select each cell and apply the conditional formats. Hence I am looking for something in vba which will help me save some time.

 

[djharish619]

.

 

[tweedle]

Thanks for your reply...

However the table you can see in the image is the table from where I have to lookup formats into another workbook. It is created with conditional formating. I manually update the table using the conditional formats and would like to see the same effect on another sheet.

The another sheet (where I have to look up these color formats) have the same categories with adjacent cells as current and previous (same as in table); but all the categories are not in the same row/column they are spread all over the sheet.

Also I cannot use conditional formatting because it helps me add only 3 conditions, whereas I have more than 3 conditions (say 10). If I use conditional formating I have to select each cell and apply the conditional formats. Hence I am looking for something in vba which will help me save some time.

The easiest way to port these is to use the format painter.
Right click the table shown, select the paint brush and apply the formats to the other workbook [possibly click entire columns to apply the CFs?].

but all the categories are not in the same row/column they are spread all over the sheet.

This will make it more difficult to apply a CF via VBA if ranges are not consistent.

 

[djharish619]

I have already applied CF via worksheet function below code and created a table (image in my previous reply). Now i would like to do vlookup kinda thing to update a chart based on this table. my email is djharish619@yahoo.co.in you can send me test mail so that i can send you a template which can explain you better.

Private Sub Worksheet_Change(ByVal Target As Range)
Application.Volatile
Dim icolor As Integer
If Not Intersect(Target, Range("Current_Qtr, Previous_Qtr, Print_Area")) Is Nothing Then
'On Error GoTo 0
Select Case Target
Case 1 To 1
icolor = 3
Case 2 To 2
icolor = 46
Case 3 To 3
icolor = 27
Case 4 To 4
icolor = 4
Case 5 To 5
icolor = 15
Case Else
'Whatever
End Select
Target.Interior.ColorIndex = icolor
Target.Font.ColorIndex = icolor
End If

End Sub

 

[Jerry Sullivan]

Hello,

Your use of the term Conditional Formatting might have led to some misunderstanding.

The CF you are referring to is not Excel's CF function, but rather Fill Color applied to each cell based on it's value.

If you want to apply the ColorIndex values you showed in your post to those three named ranges using a macro, you could try the code below.

Sub Apply_Fill_Color()
    Dim icolor As Integer
    Dim c As Range
    
    For Each c In Range("Current_Qtr, Previous_Qtr, Print_Area")
        Select Case c.Value
            Case 1 To 1
                icolor = 3
            Case 2 To 2
                icolor = 46
            Case 3 To 3
                icolor = 27
            Case 4 To 4
                icolor = 4
            Case 5 To 5
                icolor = 15
            Case Else
                icolor = -4142 'No fill
        End Select
        c.Interior.ColorIndex = icolor
       ' c.Font.ColorIndex = icolor
    Next c
End Sub

I've commented out the statement that changed the font color to the same color as the cell's fill color - you won't be able to see your values if you do that.