CODEWITHSUNDEEP
excel
ashish
ashish

Reputation: 35

formatting the cell color in excel without VBA

I have a client situation where I can not use the VBA code and have to change the formatting of cells. The data is NxM with row headers. In each of the rows I have to color 1st maximum value as RED, 2nd maximum value as BLUE and 3rd maximum value as YELLOW. Assume the row wise data is unsorted, non repeated and of data type LONG. Progress so far: I have calculated 1, 2 , 3 maximum using HLOOKUP and MAX functions. Based on these values and conditional formatting I have created the attached sample matrix. From this I was trying to copy the FORMAT and apply it to the original data but failed in doing so. The attached images are just sample data not the original one and the above mentioned assumptions holds. enter image description here

enter image description here

Upvotes: 0

Views: 650

Answers (1)

PQ Co
PQ Co

Reputation: 517

Try using conditional formatting:

Create 3 new rules and apply each to columns A1:K<lastRow>:

=INDIRECT(CHAR(COLUMN()+64) & ROW()) = INDIRECT("M" & ROW())

=INDIRECT(CHAR(COLUMN()+64) & ROW()) = INDIRECT("N" & ROW())

=INDIRECT(CHAR(COLUMN()+64) & ROW()) = INDIRECT("O" & ROW())

Upvotes: 1

Related Questions