Fetching value of Highlighted Cell

[ananthram]

Hi All,


Please help me with below find, which has Highlighted cells like option A, B, C & D. so when Option D is highlighted i need the Cell Value of it in Column Answer Key. same follows to each and every rows.


https://www.box.com/s/xf8h8qujh7mnsjxclw6n

 

[NARAYANK991]

Hi Ananth Ram ,


Try this :

[pre]

Public Sub Answer_Key()
Dim COLORLESS As Long
COLORLESS = RGB(255, 255, 255)

Dim wrk_range As Range
Set wrk_range = Range("A2:D36")

For Each cell In wrk_range
If cell.Interior.Color <> COLORLESS Then
cell.Offset(0, Columns("E").Column - cell.Column).Value = cell
End If
Next
Columns("E").EntireColumn.AutoFit
End Sub

[/pre]
Narayan

 

[ananthram]

Sir,


Thank you but what i need is the option ID, that is if option D is highlighted i need D as answer key. same follows to other rows

 

[NARAYANK991]

Hi Ananth Ram ,


Try this :

[pre]

Public Sub Answer_Key()
Dim COLORLESS As Long
COLORLESS = RGB(255, 255, 255)

Dim Options_Array() As Variant
Options_Array = Range("A1:D1").Value

Dim wrk_range As Range
Set wrk_range = Range("A2:D36")

For Each cell In wrk_range
If cell.Interior.Color <> COLORLESS Then
Cells(cell.Row, "E").Value = Options_Array(1, cell.Column)
End If
Next
Columns("E").EntireColumn.AutoFit
End Sub

[/pre]
Narayan

 

[ananthram]

Wew... thank you Narayan Sir... works like a charm