VBA help - index and named ranges (also colours)

[GeorgeF211]

Hi all,

I'm still learning a lot about VBA, so I'm sure this first one is a relatively simple question:

I'm after a function that does exactly the same thing as INDEX when used in a spreadsheet, specifically when working with a named range.


The second question I think is a little more complex, but might also be fairly trivial:

is there a way to copy the color index (or RGB code) of the filling of a cell.


Sorry if this is answered somewhere else, but I couldn't find it with the search.


Cheers,


George

 

[Luke M]

Q1:

MyValue = WorksheetFunction.Index(Range("Named_Range",5)


Q2:

ColorValue = ActiveCell.Interior.ColorIndex

*Note: If for Q2 you want to know the conditional formatting that is currently being applied, things get a little trickier

 

[GeorgeF211]

Cheers Luke.


So I'm clear, does WorksheetFunction basically allow you to use standard excel commands?


is there a way of working Q2 without using ActiveCell? Say for example I wanted to fill Cell A1 with the same colour as B1, how would I go about that?

 

[Luke M]

There's some limit as to which functions the WorksheetFunction library supports, so I usually check the VB dropdown that appears. Most of them work okay.


Yeah, you can reference any cell object that you want. For your example:

Range("A1").Interior.ColorIndex = Range("B2").Interior.ColorIndex

 

[GeorgeF211]

I'm having a little bit of an issue combining these to copy the format from the indexed cell.


Thanks for this dude .

 

[Luke M]

Make sure you have MyValue defined as a range and not a string/undefined.

 

[GeorgeF211]

Right now I'm using it to fill cells in order by cycling through a range of values, so it's an element in an array, is there a way of forcing it to behave like it's an array too?


For clarity this is the code I'm using:

[pre]

Public Sub Worksheet_Change(ByVal Target As Range)
Dim MyRange As Range
Dim ColCounter As Integer
Dim RowCounter As Integer

Set MyRange = Range("E4:BL119")                             'This is where the data starts

RowCounter = 1
ColCounter = 1

For Each Cell In MyRange                                    'for all of the prices

If RowCounter = ActiveCell.Row Then                     'if the row hasn't changed

ColCounter = ColCounter + 1                         'Move along one column

If ColCounter = 2 Then                              'If we're at £POR (2nd col of 3)
If Cell.Value > Range("D127").Value Then        'lowest positive check
Cell.Interior.ColorIndex = 46               'dark orange
End If

If Cell.Value > Range("D126").Value Then
Cell.Interior.ColorIndex = 44               'light orange
End If

If Cell.Value > Range("D125").Value Then
Cell.Interior.ColorIndex = 6                'yellow
End If

If Cell.Value > Range("D124").Value Then
Cell.Interior.ColorIndex = 43               'lime green
End If

If Cell.Value > Range("D123").Value Then
Cell.Interior.ColorIndex = 4                'pure green
End If

If Cell.Value < 0 Then                          'if negative
Cell.Interior.ColorIndex = 3                'red
End If

If Cell.Value = "" Then                         'if empty
Cell.Interior.ColorIndex = 2                'white
End If

End If

If ColCounter = 3 Then                              'if we're at %POR (3rd col of 3)

If Cell.Value > Range("C127").Value Then
Cell.Interior.ColorIndex = 46
End If

If Cell.Value > Range("C126").Value Then
Cell.Interior.ColorIndex = 44
End If
If Cell.Value > Range("C125").Value Then
Cell.Interior.ColorIndex = 6
End If

If Cell.Value > Range("C124").Value Then
Cell.Interior.ColorIndex = 43
End If

If Cell.Value > Range("C123").Value Then
Cell.Interior.ColorIndex = 4
End If

If Cell.Value < 0 Then
Cell.Interior.ColorIndex = 3
End If

If Cell.Value = "" Then
Cell.Interior.ColorIndex = 2
End If
ColCounter = 0                                      'Restart our column loop (so that we do the same operation on G, J, M etc)

End If

End If

RowCounter = ActiveCell.Row                             'Set RowCounter to the current row (used above)

Next

End Sub

[/pre]

Data is formatted in as follows: E = sale price, F = profit (£), G = Profit (%). This cycles through in this order until Col BL.

 

[Luke M]

I'm afraid I'm not sure where you're wanting to implement reading a cell's color in your current code. Could you add a comment showing where you're having trouble? Current macro looks good...although you may want to change to some

ElseIf

statements so that you don't have to repeated check the cell value every time, unless this is working like conditional formatting, and you DO want to check every comparison. I'm not sure.

 

[GeorgeF211]

yeah, it's replacing conditional formatting so I need to use the check.


Basically what I'm looking to do is have the macro look at the named range Key and take the format form those colours. I'll need to change the cell references to C123 through C127 to references to Key. Does that make sense?

 

[Luke M]

focusing on the last half of your code, I think this is what you're going for. Checks the cell's value against every cell within the KeyRange, and if criteria is met than match the color index.

[pre]

Dim KeyRange As Range
Dim c As Range
Set KeyRange = Range("C123:C127")

If ColCounter = 3 Then  'if we're at %POR (3rd col of 3)
For Each c In KeyRange
If cell.Value > c.Value Then
cell.Interior.ColorIndex = c.Interior.ColorIndex
End If
Next c

ColCounter = 0      'Restart our column loop (so that we do the same operation on G, J, M etc)

End If

[/pre]

 

[GeorgeF211]

ooh, good solution, this would make it way easier to expand and add conditions when necessary too. Would I be better served using RGB over colour index?